Participation Project #05 - Formulas & Functions I

Formulas

Formulas always start with an equal sign! If you’re trying to do anything mathematical or use a function, you have to put an “=” sign first before you type anything else.

If the formula is given to you on the PDF, you can copy and paste it directly from the PDF into your cell. Be careful here of two things here – (1) if you have a leading space before your equal sign, you have to delete the space or Excel will not recognize that it’s a formula; (2)  if it’s a formula that wraps onto a second line, sometimes Excel will insert a hard return between the lines that has to be deleted before it will evaluate the formula. You can expand your cell by clicking the down arrow in the formula bar on the right.

The f(x) at the front of the formula bar (just to the left of Tons of Coal Mined per County in the image above) has options for common formulas like MIN, MAX, AVERAGE, etc. There is a search bar that you can use to find formulas once you click on it.

If the formula is not given to you, sometimes the easiest way to insert the formula is by clicking on the appropriate cells. For step 5A, click on the value for 2019 for the first row in your table, type in your “/” symbol, then click on the value for 2019 in the Totals Row. In tables, Excel will generally autofill the formula once you enter it into the first cell.

If you use the f(x) in the menu bar to insert a predefined function, such as =RANK.EQ(), Excel gives you hints for what goes where and what is required to successfully evaluate the formula.

Because I have my cursor in the number field, this tells me at the bottom that the Number is the number for which you want to find the rank.

Absolute and Mixed Reference

When creating absolute cell references, this means putting a “$” in front of both the letter and the number in your formula. The letters are references to the columns and the numbers are references to the rows. If you want a column absolute mixed reference, the “$” goes in front of the letter only. If you want a row absolute mixed reference, the “$” goes in front of the number only. If the directions say “absolute cell reference” and don’t indicate a mixed reference to a column or a row, the “$” goes in front of both.