Topic 4
Name the 3 'IF' functions
=COUNTIF
=SUMIF
=AVERAGEIF
How do you begin writing a formula in Excel?
=
TRUE or FALSE. You should never reference a cell when writing functions/ formulas; write the numerical values into the function/ formula.
False. Always reference cells when you can so that your data automatically updates itself.
TRUE or FALSE. The LARGE function determines the 'k-th' highest value.
TRUE.
TRUE or FALSE. The Currency format aligns the dollar symbol to the far left of the cell and aligns the decimal places of the values.
FALSE. The Currency format places the dollar symbol next to the value and does NOT align the decimal places. This type of formatting is describing the Accounting format.
TRUE or FALSE. When inputting a criteria argument, numeric values should be placed between quotation marks.
FALSE. Numeric values should be displayed by themselves in the argument. =COUNTIF(A1:A10, 100)
If I want my cell references to stay the same when I drag my formula, what is the name of the thing I have to do to them? It is indicated by the $ symbol.
Absolute Reference
True or false. Excel always evaluates your formulas from left to right in calculating your answers.
FALSE! Excel uses the Order of Operations to evaluate your formulas.
I want to add all of column B values that meet a certain criteria in column A. What function can I use?
=SUMIF
TRUE or FALSE. 3D charts are always a good idea when creating a chart. They are easy to read and a visually appealing way to display your data.
FALSE. 3D charts are hardly ever a good idea. They can skew the data and make it difficult to read exact values form the chart.
TRUE or FALSE. Text in the criteria portion of an argument does not have to be exact and can have spaces in front of or behind it.
FALSE. Spaces DO count as characters in a text criteria. They must deleted or accommodated using other methods.
Write a function/formula to add these cells: A1, A2, A3, B4, B5, B6, C1
=SUM(A1:A3,B4:B6,C1)
=SUM(A1, A2, A3, B4, B5, B6, C1)
=A1+A2+A3+B4+B5+B6+C1
TRUE or FALSE. Displayed data are the formulas and functions in a cell.
FALSE! It is what shows up in a cell. Formulas and functions are the Stored Data.
If I want to randomly generate a set of numbers in excel, what function can I use to do that? State the specific function.
=RANDBETWEEN
The name of a small chart placed inside a single cell is ___________________.
Sparkline
List the 5 ways to input the Criteria argument in the IF functions.
1. Numeric 2. Text 3. Cell Reference 4. Relational Operators 5. Wildcards
If you were to drag the following formula to the right two cells (across two columns), what would the new formula be? =SUM(A$10:$A15). Be specific.
=SUM(C$10:$A15)
What is the symbol to write a Greater Than Or Equal To sign in Excel?
>=
In the AVERAGEIF function it calls for 3 arguments: (range, criteria, average_range). What does the "range" argument refer to? …be specific.
The range indicates the area you want excel to "lookup" the criteria to meet or not.
Name the most appropriate chart for showing the contribution of each store to the entire corporation.
Pie Chart
Inside any function, brackets around an argument mean ________________.
....you do not have to include the argument because there is a default option available.
How would a I reference cell C7 on a separate spreadsheet named FSU Football? (Hint: there is a space between FSU and Football)
=‘FSU Football’!C7
What is the difference between the COUNT and COUNTA functions? Be specific.
The COUNT function is generally used to count cells with numerical values in them. It ignores empty cells and cells with text in them. The COUNTA function is generally used to count cells with text in them. It only ignores empty cells.
Write a function/ formula to round cell C20 to the nearest tenth.
=ROUND(C20,1)
Which chart should you use to illustrate the relationship between two numeric variables.
Scatter plot. Line chart displays data with one numeric variable and one categorical variable.