Basics: doing math with Excel

(You can Add, Subtract, Multiply, and Divide in Excel without using any built-in functions. You just
need to use some basic operators: +, -, *, /. All formulas start with an equals (=) sign).

To Add, select cell F3, type =C3+C4, then press Enter.

To Subtract, select cell F4, type =C3-C4, then press Enter.
To Multiply, select cell F5, type =C3*C4, then press Enter.
To Divide, select cell F6, type =C3/C4, then press Enter.

AVERAGE function

(Use the AVERAGE function to get the average of numbers in a range of cells.)

1. Select cell D7, then use AutoSum to add an AVERAGE function.

2. Now select cell G7, and enter an AVERAGE function by typing =AVERAGE(G3:G6).
3. In cell D15, you can use either AutoSum, or type to enter another AVERAGE function.

MIN and MAX functions

Use the MIN function to get the smallest number in a range of cells.
Use the MAX function to get the largest number in a range of cells.

1. Select cell D7, then use the AutoSum Wizard to add a MIN function.
2. Now select cell G7, and enter a MAX function by typing =MAX(D3:D6).
3. In cell D15, you can use either the AutoSum Wizard, or type to enter a MIN or MAX function.

Date functions

(Excel can give you the current date, based on your computer's regional settings. You can also add
and subtract Dates).

1. Check out the TODAY function, which gives you Today's date. These are live, or volatile functions,
so when you open your workbook tomorrow, it will have tomorrow's date. Enter =TODAY() in cell
D6.
2. Subtract Dates - Enter your next birthday in MM/DD/YY format in cell D7, and watch Excel tell you
how many days away it is by using =D7-D6 in cell D8.
3. Add Dates - Let's say you want to know what date a bill is due, or when you need to return a
library book. You can add days to a date to find out. In cell D10, enter a random number of days. In
cell D11, we added =D6+D10 to calculate the due date from today.

Joining text from different cells

(There are many times in Excel when you want to join text that's in different cells. This example is
very common, where you have first and last names, and want to combine them as first name, last
name, or full name. Fortunately, Excel lets us do that with the Ampersand (&) sign, which you can
enter with Shift+7).

1. In cell E3, enter =D3&C3 to join the last and first names.
2. SmithNancy doesn't look quite right though. We need to add a comma and a space. To do that
we'll use quotes to create a new text string. This time, enter =D3&", "&C3. The &", "& portion lets
us join a comma and space with the text in the cells.
3. To create the full name, we'll join first and last name, but use a space without a comma. In F3,
enter =C3&" "&D3.
IF statements

(IF statements allow you to make logical comparisons between conditions. An IF statement generally
says that if one condition is true do something, otherwise do something else. The formulas can return
text, values, or even more calculations).

1. In cell D9 enter =IF(C9="Apple",TRUE,FALSE). The correct answer is TRUE.

2. Copy D9 to D10. The answer here should be FALSE, because an orange is not an apple.
3. Try another example by looking at the formula in cell D12. We got you started with
=IF(C12<100,"Less than 100","Greater than or equal to 100"). What happens if you enter a number
greater than or equal to 100 in cell C12?

IF statement with another function

(IF statements can also force additional calculations to be performed if a certain condition is met.
Here we're going to evaluate a cell to see if Sales Tax should be charged, and calculate it if the
condition is true).

1. In cell F33, we've entered =IF(E33="Yes",F31*SalesTax,0), where we set up SalesTax as a Named

Range with a value of 0.0825. Our formula says If cell E33 equals Yes, then multiply cell F31 times
SalesTax, otherwise return a 0.

Try changing Yes to No in cell E33 to see the calculation change.

2. Next we've added an IF statement to calculate shipping if it's required. In cell F35 you'll see
=IF(E35="Yes",SUM(D28:D29)*1.25,0). This says "If cell E35 is Yes, then take the sum of the Quantity
column in the table above, and multiply it by 1.25, otherwise return a 0".

3. Next, change the 1.25 in the formula in cell F35 to "Shipping". As you start typing, Excel's auto-
correct, should find it for you. When it does, press Tab to enter it. This is a Named Range, and we
entered it from Formulas > Define Name. Now, if you ever need to change your shipping cost, you
only have to do it in one place, and you can use the Shipping name anywhere in the workbook.

VLOOKUP

(VLOOKUP is one of the most widely used functions in Excel (and one of our favorites too!). VLOOKUP
lets you look up a value in a column on the left, then returns information in another column to the
right if it finds a match. VLOOKUP says):

1. In cell D22, enter =VLOOKUP(C22,C17:D20,2,FALSE). The correct answer for Apples is 50.
VLOOKUP looked for Apples, found it, then went over one column to the right, and returned the
amount.
2.Now try for yourself in the Meat section, in cell G22. You should end up with
=VLOOKUP(F22,F17:G20,2,FALSE).
VLOOKUP and #N/A

(Invariably, you'll run into a situation where VLOOKUP can't find what you asked it to, and it returns
an error (#N/A). Sometimes, it's because the lookup value simply doesn't exist, or it can because the
reference cell doesn't have a value yet).

1. If you know your lookup value exists, but want to hide the error if the lookup cell is blank, you can
use an IF statement. In this case, we'll wrap our existing VLOOKUP formula like this in cell D43:

=IF(C43="","",VLOOKUP(C43,C37:D41,2,FALSE))

This says, "If cell C43 equals nothing (""), then return nothing, otherwise return the VLOOKUP's
results". Note the second closing parenthesis at the end of the formula. This closes the IF statement.

2. If you're not sure your lookup value exists, but you still want to suppress the #N/A error, you can
use an error handling function called IFERROR in cell G43:
=IFERROR(VLOOKUP(F43,F37:G41,2,FALSE),""). IFERROR says, "If the VLOOKUP returns a valid
result, then display that, otherwise, display nothing ("")". We displayed nothing here (""), but you
can also use numbers (0,1, 2, etc.), or text, such as "Formula isn't correct".

Conditional functions - SUMIF

(Conditional functions let you sum, average, count or get the min or max of a range based on a given
condition, or criteria you specify. Such as, out of all the fruits in the list, how many are apples? Or,
how many oranges are the Florida type?)

1. SUMIF lets you sum in one range based on a specifc criteria you look for in another range, like
how many Apples you have. Select cell D17 and type =SUMIF(C3:C14,C17,D3:D14). SUMIF is
structured like this:

2. SUMIFS is the same as SUMIF, but it lets you use multiple criteria. So in this example, you can look
for Fruit and Type, instead of just by Fruit. Select cell H17 and type
=SUMIFS(H3:H14,F3:F14,F17,G3:G14,G17). SUMIFS is structured like this:

Conditional functions - COUNTIF

(COUNTIF and COUNTIFS let you count values in a range based on a criteria you specify. They're a bit
different from the other IF and IFS functions, in that they only have a criteria range, and criteria. They
don't evalute one range, then look in another to summarize).

1. Select cell D64 and type =COUNTIF(C50:C61,C64). COUNTIF is structured like this:

2. COUNTIFS is the same as SUMIF, but it lets you use multiple criteria. So in this example, you can
look for Fruit and Type, instead of just by Fruit. Select cell H64 and type
=COUNTIFS(F50:F61,F64,G50:G61,G64). COUNTIFS is structured like this: