Insert subtotals into the range B2:G12, with the subtotals appearing at each change in the Instrument column value. The subtotals should use the SUM function and include subtotals
pls hlp with these qnss:
Naomi and Kaito have more string instruments than any other type. Naomi wants to summarize the string instrument data using subtotals to show the value of each type. Switch to the Strings worksheet and add subtotals as follows:
a. Sort the table by the Instrument field in ascending order.
b. Convert the table to a normal range.
c. Insert subtotals into the range B2:G12, with the subtotals appearing at each change in the Instrument column value. The subtotals should use the SUM function and include subtotals for the Price and Est. Value fields.
Switch to the Wind worksheet and remove the duplicate record based on the values in the Instrument, Manufacturer, and Condition columns. The duplicate record has an incorrect Purchase Date of 7/11/2020.
The data bars in the last two columns make some of the numbers hard to read and could coordinate better with the formatting of the Wind table.
Change the Data Bars conditional formatting for the range F3:G12 to use the Gradient Fill Orange Data Bar format.
Naomi wants to calculate the totals for the instrument collection data. Switch to the All Instruments worksheet, and then create the following formulas:
a. In cell B40, use the COUNTA function with a structured reference to count the values in the Instrument column of the Instruments table.
b. In cell F40, use the SUM function with a structured reference to total the values in the Price column of the Instruments table.
c. In cell G40, use the SUM function with a structured reference to total the values in the Est. Value column of the Instruments table.
Naomi and Kaito are preparing to sell some of the instruments, and Naomi wants to include the sales date in the Instruments table
Add a table column to the end of the table.
a) Go to the Strings worksheet. Click on dropdown of Instrument and select A to Z. b) Select table > Table Design tab > click Convert in range from tools group. c) slect table. Data tab > click Subtotal from Outline group > Select Price ans Est. Value.
Go to the Wind worksheet. Click any cell of the table > Data tab > Remove duplicate from Data Tools group > Select Instrument, Manufacturer and Condition and hit OK.
Select F3 through G12 > Home tab > Conditional formating > Data Bars > choose Gradient Fill Orange
a) Click B40 and enter =counta(Instruments[Instrument]). b) Click F40 and enter =sum(Instruments[Price]). c) Click G40 and enter =sum(Instruments[Est. Value])
Click on Est. Value > right click > Insert > Table Column to the Right
=counta(Instruments[Instrument]). b) Click F40 and enter=sum(Instruments[Price]). c) Click G40 and enter=sum(Instruments[Est. Value])Video instructions: