Functions
While you can get many things accomplished with formulas, functions have many more uses beyond just math. Functions, like formulas, must always have an "=" at the front of the cell in order to work.
The Function Builder
There are a lot of functions in Excel, far too numerous to be taught in just one lab. However, Excel has a built in feature called the function builder which provides a list of all built-in functions, what they do, and what you need to provide for them to work.
- In both web ,app version, the function builder is found in the "Formulas" tab
Once you have the function builder open, you should see a list of functions. You can search individual categories, such as "Financial", from the dropdown. Once you select a function, you will be able to see what it does and what you need to enter.
IF Function
The IF function, found in the "logical" category, is a true or false test. If a condition is true, it will display one thing, if the condition is false it will display the other.
For example, we could place the following function in B16:
=IF(B11>100, "High", "Low")
This function checks if the number in B11 is greater than 100. If it is, it will print the word "high", because it is higher than 100. If it is false, it will print the word "Low", because it is lower than 100.
To see this, you could fill it to the right a few spaces. Since B11 is greater than 100, it will say "high", but the next numbers will say "low" because they are less than 100.
Nesting Functions
You can put functions within functions to accomplish multiple things at once. This is called nesting functions. When using nesting functions, it is important to keep track of all commas and brackets,
because having one out of place can ruin the whole thing, and they can be tricky to troubleshoot.
For this example, we will pretend we have a PST of 7% and a GST of 5%, both together being 12%.
- In A18, type "Tax Code", in B18 type "Price", and in C18 Type "Taxes"
- In A19 type "P", in A20 type "G", in A21 type "B". These are the first letters of each of our tax types. It is important that they are capitalized for later
- In B19, B20, and B21 type "100" for each price
- In C19, enter the following formula:
=IF(A19="P", B19 * 0.07, IF(A19="G", B19 * 0.05, IF(A19="B", B19 * .12, "Unknown")))
In C19, you will get a 7. This is because the first if statement finds the tax code P, which has a rate of 7%, and multplies it by 100. If you fill down, you should get 5 in the next row. Once the first statement fails, it checks for a G, and upon finding it mulplies 100 and 5%. The third column will give you 12, because after the first two tests fail, it finds a B and multiplies 100 by 12%. If you fill one more down, it will write "unknown", because there is no code in A22 and all three tests will fail, leaving it on the last option.
Functions on the lab test
Functions will be very important to the lab test. Remember that since the lab test is open book (including the internet), and you've been shown a feature that describes how to use every function, we consider any function fair game. This doesn't mean you need to know how every function works - you may need to know how to put functions in until one gives you the result you want.