Formulas Review

Whenever you do a series of calculations, you are using a formula, whether or not you ever write it down. To write a formula for Excel to use, you must know two things: where to find the numbers to use (by cell reference or cell name) and what to do with them (which operations to use).


Operations

The basic operations and their symbols are:

power ^    add +    subtract -    multiply *    divide /

Raising a number to a power is just multiplying the number by itself. So if the value in cell G4 is 8, then G4^4 means 8*8*8*8 = 4096, that is, 8 times itself 4 times.

While technically not operations, you will also need to understand negation - (the negative of a number) and percentage % (the form of the number as a percent). If cell D10 contains the number 23, then in a formula -D10 is the number -23. If cell D10 contains the number -15, then -D10 means -(-15) which is the same as 15. The percentage form of a number moves the decimal place in the number. So 125% is the same as the number 1.25 and 50% is the same as .5 and .50 and .50000.


Order of operations

When there are several operations involved, you have to know what order to do them in.

 

Order of operations:

1

Any calculations inside parentheses ( )

2

Negation -

3

Percentage %

4

Powers ^

5

Multiply and divide * /

6

Add and subtract. + -

When a formula uses more than one operation, the operations must be done in the right order. For example, in 3 + 5 / 8 you must divide first and then add 3, which results in an answer of 3.625. This is not the same as (3 + 5)/8 for which you add first (because of the parentheses) and then divide, resulting in an answer of 1.


Formula Examples

Here are some sample Excel formulas and what they mean. The equals sign is required. Parentheses are required around arguments for a function, like the range of cells in the AVERAGE function. New for 97 When your cells are coming from rows and columns, you can use the row and column labels in your formulas instead of the cell references. These are natural language formulas.

Formula

Means:

= B3*C14

Multiply the value in cell B3 by the value in C14

=Quantity*Cost each

Multiply the value in this row of the column labeled "Quantity" by the value in this row of the column labeled "Cost each".

= D12-B3

Subtract the value in cell B3 from the value in D12

= B3^3

Raise the value in B3 to the power 3. This is the same as B3*B3*B3

=(A10 +B5)/C4

Add the values in cells A10 and B5 and then divide by the value in C4.

=SUM(B3:C14)

Add together the values in the range

=AVERAGE(D4:G45)

Add up the values in range D4:G45 and divide by the number of values there were.

=MAX(J300:M350)

Find the largest value in the range J300:M350

=MIN(F12:F20)

Find the smallest value in the range F12:F20

=COUNT(K20:L50)

Count the number of values there are in the range K20:L50. (This will skip any blank cells, but counts cells containing a zero.

Practice using formulas in Exercise: Formulas


Working with Numbers Appendix




Teachers: Request permission to use this site with your class

Copyright 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD
Want to help?


~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~