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.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).
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.
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.
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.
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
|
Teachers: Request permission to use this site with your class Copyright © 1997-2012 Jan Smith <jegs1@jegsworks.com>All Rights Reserved |
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~
