Practical Functions and Uses
For versions 7.0 and later
"To recover info from a full workbook, link in current workbook with unusable workbook, then copy down"
the page to see what shows up
= 'c:\my documents\[summary Data]sheet1'!a1
Adds all values in A1 from each sheet: sheet 2 to sheet 10
=SUM(sheet2:Sheet10!a1)
Joins John and Smith with exclamation point in between
=INDIRECT(""a11"")&(""!"")&INDIRECT(""B11"")
John Smith
John!Smith
"Joins John And Smith. For a space between the words, either a space at the end of John"
or a space before Smith is inserted in the cell of each word. The & is the same as CONCATENATE
"See Prac Routines ""JoinMe"" for VBA of this type of method."
" =CONCATENATE(A25,B25)"
John Smith
John Smith
"Unjoin John Smith, two Functions are required. =LEFT and =RIGHT with the Find Method embedded."
"With the Find Method, make sure a space is between the Quotes."
" =LEFT(A26,FIND("" "",A26))"
John
" =RIGHT(A26,FIND("" "",A26))"
Smith
Counts all of a criteria in a range
" =COUNTIF(A1:A10,""YES"")"
7
yes no yes
no yes yes
no yes no
no no no
yes - yes
Shows the cell value of C4
=4:4 C:C =C:C 4:4
Column C:C is named January. The result is cell value for C4
=4:4 January
"Row 4:4 is named Bills_Transport_Company, and Column C:C is named January_Sales, then the cell "
value is C4
=Bills_Tansport_Company January_Sales
"Row 4:4 is named Bills_Transport_Company, and Column C:C then cell value is C4"
=Bills_Tansport_Company C:C
"Cell A1 is 790,930,890. To show only the first four numbers 7909"
" =LEFT(A1,4)"
If a cell in column A:A of sheet 2 is the same as a cell in column A:A in sheet 1 then the value of
"the match is shown in column D:D in sheet 1 on the same Row, copied down column D:D"
" =IF(ISERROR(IF(MATCH(A:A+Sheet2!A:A,SHEET1!A:A,A:A),A2))=TRUE,"""",A2)"
"A message using the IF function, deducting three days from Due Date"
" =IF(B87-3=A87,""Due in 3 Days"",""Not Due"")"
10/09/00 11/09/00 Not Due
"A message using the IF function that shows when an item is due: 3 days, 2 days, 1 day, Due Today"
8/09/00 11/09/00 9/09/00 11/09/00 10/09/00 11/09/00 11/09/00 11/09/00
Due in 3 Days Due in 2 Days Due in 1 day DueToday
" =IF(B94-3=A94,""Due in 3 Days"",IF(B94-2=A94,""Due in 2 Days"",IF(B94-1=A94,""Due in 1 day"","
" IF(B94-0=A94,""DueToday""))))"
Character to define trademarks and other useful features
� =CHAR(225) � =CHAR(233)
� =CHAR(163) � =CHAR(153)
� =CHAR(169) � =CHAR(174)
Example: caf � caf� " =CONCATENATE(C109,D109)"
To show a number without the decimals
=FLOOR(456.78,2) 456.78 456 " =FLOOR(D117,2)"
=GCD(456.78) 456.78 456 =GCD(D113)
=INT(456.78) 456.78 456 =INT(D113)
=LCM(456.78) 456.78 456 =LCM(D121)
=LEFT(456.78,3) 456.78 456 " =LEFT(D123,3)"
=MROUND(456.78,3) 456.78 456 " =MROUND(D125,3)"
=ROUNDDOWN(456.78,0) 456.78 456 " =ROUNDDOWN(D115,0)"
=SUBSTITUTE(D128,456.78,456) 456.78 456 =SUBSTITUTE(D128,D128,456)
=TRUNC(456.78) 456.78 456 =TRUNC(D128)
=LEFT(D133,FIND(""6"",456.78)) 456.78 456 " =LEFT(D133,FIND(""6"",D133))"
"Testing whether data entered is text, value, or nothing, there sre several functions to do this deed. If"
"the answer is true then the result is TRUE, if false then FALSE is the result"
text formula result
text =ISTEXT(A140) TRUE
1234 =ISNUMBER(A141) TRUE
=ISBLANK(A142) TRUE
1234 =ISTEXT(A140) FALSE
text =ISNUMBER(A141) FALSE
=ISBLANK(A142) FALSE
The AND function requires any arguments are TRUE for it to return a TRUE result. The OR function
only requires one argument to be TRUE for it to return a TRUE result.
To calculate a number of months after a start date eg: start date is 30 June 2000 You must either use
a cell reference or a date serial number as the start date
30-Jun-00 36707 =A157
" =EOMONTH(Startdate,months)" " =EOMONTH(B157,6)"
31-Dec-00 36891 " =EOMONTH(36707,6)" Format the answer into a date format
Change a date format to a serial number
=ABS(DateFormat) =ABS(A159) 36891
Counts all of a criteria in a range
Area Type Units Sold Unit Cost Totals
" =SUMIF(Type,criteria,Units_Sold)" Area1 Coke 20 $1.05 $21.00
Total Coke Units Sold 55 Area1 Solo 50 $1.02 $51.00
Total Solo Units Sold 145 Area1 Tarax 100 $0.99 $99.00
Total Tarax Units Sold 300 Area2 Coke 25 $1.05 $26.25
Area2 Solo 55 $1.02 $56.10
" =SUMIF(Area,criteria,Totals)" Area2 Tarax 110 $0.99 $108.90
Total Area1 Sold $171.00 Area3 Coke 10 $1.05 $10.50
Total Area2 Sold $191.25 Area3 Solo 40 $1.02 $40.80
Total Area3 Sold $140.40 Area3 Tarax 90 $0.99 $89.10
Totals 9 9 500 $9.18 $502.65
" =SUBTOTAL(Type,Ref)" -1 excludes title
Type refers to a calculation type
Area Type Units Sold Unit Cost Totals
1 AVERAGE average of values 55.56 $1.02 $55.85
2 COUNT count numeric values 9 9 9
3 COUNTA count non-blank values 10 10 10 10 10
4 MAX maximum value 110 $1.05 $108.90
5 MIN minimum value 10 $0.99 $10.50
6 PRODUCT multiplies all values
7 STDEV standard deviation
8 STDEVP standard deviation
9 SUM add all values 500 $9.18 $502.65
10 VAR variance
11 VARP variance
" =VLOOKUP(LookupValue,LookupRef,ColumnNo)"
Tarax ??
" =MATCH(LookupValue,LookupRef,Type)"
Type
0 exact match else #N/A
1 less than or equal to value
-1 greater than or equal to value
" =INDEX(LookupRange,CoOrdinate1,CoOrdinate2,AreaNum)"
use in conjunction with MATCH for lookups
use to refer to a cell in a range when row or column is variable
use to refer to one- or two-dimensional ranges
100
CoOrdinate1 is row reference to LookupRange
CoOrdinate2 is column reference to LookupRange