MS Excel Learning Classes – 6

Logical Functions

A function is a predefined formula that performs a particular type of computation. All you have to do to use a function is supply the values that the function uses when performing its calculations; these are the arguments of the function.

SUM, COUNT, MAX, AVERAGE are all examples of functions. The function IF is a logical function.

A logical function can only return one of two possible results: TRUE or FALSE. For example:

=A1<25 produces the logical value TRUE if cell A1 contains a value less than 25 (otherwise the formula produces the logical value FALSE).

The IF function is frequently used because it can test for a particular condition in the worksheet and use one value if the condition is true and another value if the condition is false.

=IF(logical_test, value_if_true,value_if_false)      

For example:

=IF(B2>=1000)

The IF condition is TRUE if B2 contains a value of 1000 or larger; the condition is FALSE if the value in B2 is less than 1000.

You can have the IF function enter text. Text must be enclosed in quote marks.

For example:

=IF(B2>=100000, “Book the cruise tickets!”,”Tidy the house – we’re staying home!”)

If you want Excel to put the value of 100 in the cell when B2 contains a value greater than or equal to 1000, but you want 50 in the cell if the B2 value is less than 1000, the formula is:

=IF(B2>=1000,100,50)

If you want Excel to put 10% of the value in B2 in the cell when B2 contains a value greater than or equal to 1000, but only 5% of the value when B2 is less than 1000, put

=IF(B2>=1000,B2*10%,B2*5%)

References

A range of cells (for example, A1:A10):

  • Uses a full colon to create one reference to all the cells in between and including the two end references in block.

Individual cells (for example, B4,D4):

  • Uses a comma. Often used in the arguments of a function.

For example:

=SUM(A2,F5)

Other sheets in a workbook:

  • To refer to other sheets in a workbook, either click and select, or type:

The name of the worksheet, then an exclamation mark (to separate sheet reference from cell reference), then the cell reference.

For example:

=Sheet2!A1

Spanning two or more sheets in a workbook

  • To sum a range of cells that span two or more sheets in a workbook:

=SUM(Sheet2:Sheet6!$A$2:$C$5)

 

Relative References

Excel adjusts the cell references and copies in a formula relative to the direction of the copying.

If the original formula in B9 is =SUM(B3:B8), when Excel copies the original formula next door to C9 it changes the formula to =SUM(C3:C8).

All new formulas naturally contain relative cell references unless you specify otherwise.

Absolute References

There will be times when you want to compare a range of values to a single value (for example, where you want to compute what percentage each part is to the total).

Start your formula and select the cell first, then press F4 (or type $ before the column number and the row number).

For example:

=B9/$E$12

Then you can AutoFill the formula to other cells.

A List of Some Useful Functions

Function What it does Example
Average The average of the values. =AVERAGE(A1:A7)
Returns the average value of the cells in the range A1 to A7.
Count Counts the number of cells that contain numbers and numbers within the list of arguments. Use COUNT to get the number of entries in a number field in a range or array of numbers. =COUNT(A1:A7)
Returns the number of cells in the range A1 to A7 containing numerical values.
Max The largest value. =MAX(A1:A7)
Min The smallest value. =MIN(A1:A7)
Pi Returns the number 3.14159265358979, the mathematical constant Pi, accurate to 15 digits. =PI()*(E7^2)
Calculates the area of a circle where the radius is stored in cell E7.
StDev An estimate of the standard deviation of a population, where the sample is all of the data to be summarized. =STDEV(A1:A7)
Sum The sum of the values. This is the default function for numeric source data. =SUM(A1:A10)
Today Today’s date. =TODAY()
Var An estimate of the variance of a population, where the sample is all of the data to be summarised. =VAR(A1:A7)

 

Spread the love
About admin 2013 Articles
Please feel free to contact us or send your comments. [email protected]