Transcript Document

Revision
Function in Spreadsheet
DATE
• Returns the serial number of a
particular date.
• Syntax
– DATE(year,month,day)
• year is a number from 1900 to 9999 in Microsoft
Excel for Windows
DATE
• Month is a number representing the month of the
year. If month is greater than 12, then month adds
that number of months to the first month in the year
specified. For example, DATE(90,14,2) returns the
serial number representing February 2, 1991.
• Date
is a number representing the day of the
month. If day is greater than the number of days in
the month specified, then day adds that number of
days to the first day in the month. For example,
DATE(91,1,35) returns the serial number
representing February 4, 1991.
DATE
• Remarks
– The DATE function is most useful in
formulas where year, month, and day are
formulas, not constants.
DATE
• Examples
– Using the 1900 date system (the
default in Microsoft Excel for Windows),
DATE(91, 1, 1) equals 33239, the serial
number corresponding to January 1,
1991.
NOW
Returns the serial number of the current date and time.
• Microsoft Excel stores dates as sequential serial
numbers so that it can perform calculations on them.
Excel stores January 1, 1900, as serial number 1 if
your workbook uses the 1900 date system. For
example, in the 1900 date system, Excel stores
January 1, 1998, as serial number 35796 because it is
35,795 days after January 1, 1900.
NOW
• Numbers to the right of the decimal point in the
serial number represent the time; numbers to the
left represent the date. For example, in the 1900
date system, the serial number 367.5 represents
the date-time combination 12:00 P.M., January 1,
1901.
• Examples
If you are using the 1900 date system and your computer's
built-in clock is set to 12:30:00 P.M., 1-Jan-1987, then:
NOW() equals 31778.52083
Ten minutes later:
NOW() equals 31778.52778
FIND
• FIND finds one text string
(find_text) within another text
string (within_text), and returns the
number of the starting position of
find_text, from the first character
of within_text.
FIND
• unlike SEARCH, FIND is case
sensitive and doesn't allow wildcard
characters.
• Syntax
– FIND(find_text,within_text,start_num)
– Find_text is the text you want to find.
FIND
– If find_text is "" (empty text), FIND
matches the first character in the
search string (that is, the character
numbered start_num or 1).
– Find_text cannot contain any wildcard
characters.
– Within_text is the text containing the
text you want to find.
FIND
– Start_num specifies the character at
which to start the search. The first
character in within_text is character
number 1. If you omit start_num, it is
assumed to be 1.
HLOOKUP
• Searches for a value in the top row of a
table or an array of values, and then
returns a value in the same column from a
row you specify in the table or array. Use
HLOOKUP when your comparison values
are located in a row across the top of a
table of data, and you want to look down a
specified number of rows.
HLOOKUP
• Syntax
– HLOOKUP(lookup_value,table_array,row
_index_num,range_lookup)
• Lookup_value is the value to be found in
the first row of the table. Lookup_value can
be a value, a reference, or a text string.
• Table_array is a table of information in
which data is looked up. Use a reference to
a range or a range name.
HLOOKUP
– The values in the first row of table_array
can be text, numbers, or logical values.
– If range_lookup is TRUE, the values in the
first row of table_array must be placed in
ascending order: ...-2, -1, 0, 1, 2,... , A-Z,
FALSE, TRUE; otherwise, HLOOKUP may not
give the correct value. If range_lookup is
FALSE, table_array does not need to be
sorted.
HLOOKUP
– Uppercase and lowercase text are equivalent.
– You can put values in ascending order, left to
right, by selecting the values and then
clicking Sort on the Data menu. Click Options,
click Sort left to right, and then click OK.
Under Sort by, click the row in the list, and
then click Ascending.
HLOOKUP
• Row_index_num
– is the row number in table_array from which
the matching value will be returned. A
row_index_num of 1 returns the first row value
in table_array, a row_index_num of 2 returns
the second row value in table_array, and so on.
If row_index_num is less than 1, HLOOKUP
returns the #VALUE! error value; if
row_index_num is greater than the number of
rows on table_array, HLOOKUP returns the
#REF! error value.
HLOOKUP
– Range_lookup
• is a logical value that specifies whether you
want HLOOKUP to find an exact match or an
approximate match. If TRUE or omitted, an
approximate match is returned. In other
words, if an exact match is not found, the
next largest value that is less than
lookup_value is returned. If FALSE,
HLOOKUP will find an exact match. If one is
not found, the error value #N/A is returned.
HLOOKUP
• Remarks
– If HLOOKUP can't find lookup_value,
and range_lookup is TRUE, it uses the
largest value that is less than
lookup_value.
– If lookup_value is smaller than the
smallest value in the first row of
table_array, HLOOKUP returns the
#N/A error value.
HLOOKUP
• Examples
– Suppose you have an inventory
worksheet of auto parts. A1:A4 contain
"Axles", 4, 5, 6. B1:B4 contain "Bearings",
4, 7, 8. C1:C4 contain "Bolts", 9, 10, 11.
– HLOOKUP("Axles", A1:C4,2,TRUE)
equals 4
HLOOKUP
– HLOOKUP("Bearings",A1:C4,3,FALSE)
equals 7
– HLOOKUP("Bearings",A1:C4,3,TRUE) equals
7
– HLOOKUP("Bolts",A1:C4,4,) equals 11
– Table_array can also be an array constant:
– HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,
TRUE) equals "c"
VLOOKUP
• Searches for a value in the leftmost
column of a table, and then returns a value
in the same row from a column you specify
in the table. Use VLOOKUP instead of
HLOOKUP when your comparison
values are located in
a column to the left
of the data you
want to find.
VLOOKUP
• Remarks
– If VLOOKUP can't find lookup_value, and
range_lookup is TRUE, it uses the largest value
that is less than or equal to lookup_value.
– If lookup_value is smaller than the smallest value in
the first column of table_array, VLOOKUP returns
the #N/A error value.
– If VLOOKUP can't find lookup_value, and
range_lookup is FALSE, VLOOKUP returns the
#N/A value.
VLOOKUP
• Examples
– On the preceding worksheet, where the range
A4:C12 is named Range:
VLOOKUP(1,Range,1,TRUE) equals 0.946
VLOOKUP(1,Range,2) equals 2.17
VLOOKUP(1,Range,3,TRUE) equals 100
VLOOKUP(.746,Range,3,FALSE) equals 200
VLOOKUP(0.1,Range,2,TRUE) equals #N/A,
because 0.1 is less than the smallest value in
column A
VLOOKUP(2,Range,2,TRUE) equals 1.71