Excel 2016 Formula Tab and Groups
On the Food Inventory worksheet, enter a function in cell G1 that counts the number of Food Items. -
✔✔Formulas Tab/ formula auditing/ countA (click on food items column)
On the Revenue worksheet, enter a formula in cell J10 that uses a function to calculate the average
%
revenue from the January column - ✔✔Formulas Tab/ formula auditing/ average(january column)
Enter a formula in cell E4 that calculates the total production of Barley and updates the results even if
the row order is changed. - ✔✔Formulas Tab/ formula auditing/ sumif(select the barley colum, type
barley,metric tons column)
0
In the Sales Commissions worksheet, insert a formula in cell G4 that references the data in the Total
Sales column and the Commission Rate in cell J3 to calculate the values in the Commission column.
Copy the formula into all the cells in the Commission column. - ✔✔Formulas Tab/ formula auditing/
=F4*J$3 (NOTE: select cell f4 with your cursor. Do not type "f4". When you select the cell, you will
see{@[TOTAL SALES]}. This appears instead of F4 because the info is setup as a table.
0
Display all of the formulas on the Cookie Sales worksheet. - ✔✔Formulas Tab/ formula auditing/
show formulas
1
On the Small Tree Sales worksheet, insert a formula in cell F20 that uses a built-in Excel function to
return the value of the smallest value in the Total column. - ✔✔Formulas Tab/ formula auditing/ min
function
In cell I7 of the New Releases worksheet, use a function to calculate the average of the Review Score
column where the System type is YCube 720. - ✔✔Formulas Tab/ Function Library group/ Average If
On the Profits worksheet, enter a formula in cell A29 that uses an Excel function to return the average
costs of the fudge flavors based on the values in the Cost column. - ✔✔Formulas Tab/ Function
Library group/ average formula for cost
On the Vehicles worksheet, in the Code column cell B4, create a formula that returns the two leftmost
letters of the vehicle style displayed in cell D4. - ✔✔Formulas Tab/ Function Library group/ left
formula
Display the formulas that are in the cells of the Qtr 2 worksheet. - ✔✔Formulas Tab/ formula
auditing/ show formulas
On the Food Inventory worksheet, enter a function in cell G1 that counts the number of Food Items. -
✔✔Formulas Tab/ formula auditing/ countA (click on food items column)
On the Revenue worksheet, enter a formula in cell J10 that uses a function to calculate the average
%
revenue from the January column - ✔✔Formulas Tab/ formula auditing/ average(january column)
Enter a formula in cell E4 that calculates the total production of Barley and updates the results even if
the row order is changed. - ✔✔Formulas Tab/ formula auditing/ sumif(select the barley colum, type
barley,metric tons column)
0
In the Sales Commissions worksheet, insert a formula in cell G4 that references the data in the Total
Sales column and the Commission Rate in cell J3 to calculate the values in the Commission column.
Copy the formula into all the cells in the Commission column. - ✔✔Formulas Tab/ formula auditing/
=F4*J$3 (NOTE: select cell f4 with your cursor. Do not type "f4". When you select the cell, you will
see{@[TOTAL SALES]}. This appears instead of F4 because the info is setup as a table.
0
Display all of the formulas on the Cookie Sales worksheet. - ✔✔Formulas Tab/ formula auditing/
show formulas
1
On the Small Tree Sales worksheet, insert a formula in cell F20 that uses a built-in Excel function to
return the value of the smallest value in the Total column. - ✔✔Formulas Tab/ formula auditing/ min
function
In cell I7 of the New Releases worksheet, use a function to calculate the average of the Review Score
column where the System type is YCube 720. - ✔✔Formulas Tab/ Function Library group/ Average If
On the Profits worksheet, enter a formula in cell A29 that uses an Excel function to return the average
costs of the fudge flavors based on the values in the Cost column. - ✔✔Formulas Tab/ Function
Library group/ average formula for cost
On the Vehicles worksheet, in the Code column cell B4, create a formula that returns the two leftmost
letters of the vehicle style displayed in cell D4. - ✔✔Formulas Tab/ Function Library group/ left
formula
Display the formulas that are in the cells of the Qtr 2 worksheet. - ✔✔Formulas Tab/ formula
auditing/ show formulas
