213k views
2 votes
In the Commission Ratesworksheet, for the cell range A2:C6, define the named range Bonus. In the Combined Sales worksheet, add a right-most column named Bonus Pay and calculate the bonus amount for each sales representative using the Bonusnamed range.

User Jithinroy
by
8.0k points

1 Answer

5 votes

Final answer:

To define the named range Bonus in the Commission Rates worksheet, select the cell range A2:C6 and click on 'Define Name'. To calculate the bonus amount using the Bonus named range, use the SUMIF function in the Combined Sales worksheet.

Step-by-step explanation:

In order to define the named range Bonus for the cell range A2:C6 in the Commission Rates worksheet, you can follow these steps:

  1. Select the cell range A2:C6.
  2. Click on the 'Formulas' tab in the toolbar.
  3. Click on the 'Define Name' button in the 'Defined Names' group.
  4. Type 'Bonus' in the 'Name' field.
  5. Click on 'OK' to define the named range.

To calculate the bonus amount for each sales representative using the Bonus named range in the Combined Sales worksheet, you can use the following formula in the right-most column (cell D2 for example):

=SUMIF(Commission Rates!A2:A6,A2,Commission Rates!C2:C6)

This formula sums up the bonus amounts for all rows in the Commission Rates worksheet where the sales representative's name matches the current row in the Combined Sales worksheet.

User Prithajnath
by
9.0k points