Hi There,
I'm currently doing my cert IV in bookkeeping and i need help with 1 exercise question.
Bruce & Paul have a monthly rewards system in place for their employees as shown below.
- Average sales for weeks 1-4 >= $3,000 10%
- Average sales for weeks 1-4 >= $2,000 7%
- Average sales for weeks 1-4 >= $1,500 3%
- Average sales for weeks 1-4 <= $1,500 No Bonus
In this exercise, you are required to do the following:
- Create a multiple nested IF statements (with the average function nested in the IF statement) in column F to display the bonus as a percentage based on the rewards system they have in place for average sales for the 4 weeks.
- Create a multiple nested IF statement in column G to display the bonus amount (bonus rate x average sales for the 4 weeks). Keep in mind that for average sales <$1,500, you will display the text "No Bonus".
SELF-CHECK: If you have done everything correctly, you should have this answer.
<tbody>
</tbody>
The formula i used to get the bonus percentage is:
=IF(AVERAGE(A30:E30)>3000,10%,IF(AVERAGE(A30:E30)>2000,7%,IF(AVERAGE(A30:E30)>1500,3%,IF(AVERAGE(A30:E30)<1500,"0%","No Valid Score"))))
My question is what formula do i use for to get the bonus amount in column G remembering that i need G34 to display No Bonus.
If i use formula =AVERAGE(B30:E30)*F30 i get the correct sum for G30:G34 but it doesn't state "No Bonus" in G34 as required.
PLEASE PLEASE HELP ME I AM GOING OUT OF MY MIND.
I have this aching feeling it is easy but i need fresh eyes.
I have only just learned how to use formulas last week and this just does not come naturally to me.
Thank you in advance for your help/support.
I'm currently doing my cert IV in bookkeeping and i need help with 1 exercise question.
Bruce & Paul have a monthly rewards system in place for their employees as shown below.
- Average sales for weeks 1-4 >= $3,000 10%
- Average sales for weeks 1-4 >= $2,000 7%
- Average sales for weeks 1-4 >= $1,500 3%
- Average sales for weeks 1-4 <= $1,500 No Bonus
In this exercise, you are required to do the following:
- Create a multiple nested IF statements (with the average function nested in the IF statement) in column F to display the bonus as a percentage based on the rewards system they have in place for average sales for the 4 weeks.
- Create a multiple nested IF statement in column G to display the bonus amount (bonus rate x average sales for the 4 weeks). Keep in mind that for average sales <$1,500, you will display the text "No Bonus".
SELF-CHECK: If you have done everything correctly, you should have this answer.
| Employee (columnA29) | Week 1 (columnB29) | Week 2 (columC29) | Week 3 (columD29) | Week 4 (columE29) | Bonus (columF29) | Bonus Amount (columG29) |
| Kina (columnA30) | $2,000.00 | $2,000.00 | $4,000.00 | $3,333.00 | 7% | $198.33 |
| Bina (columnA31) | $1,000.00 | $2,345.00 | $2,000.00 | $2,121.00 | 3% | $56.00 |
| Tina (columnA32) | $2,500.00 | $2,000.00 | $2,345.00 | $1,000.00 | 3% | $58.84 |
| Lina (columnA33) | $4,000.00 | $3,214.00 | $2,000.00 | $4,000.00 | 10% | $330.35 |
| Gina (columnA34) | $800.00 | $1,800.00 | $1,000.00 | $1,500.00 | 0% | No Bonus |
<tbody>
</tbody>
The formula i used to get the bonus percentage is:
=IF(AVERAGE(A30:E30)>3000,10%,IF(AVERAGE(A30:E30)>2000,7%,IF(AVERAGE(A30:E30)>1500,3%,IF(AVERAGE(A30:E30)<1500,"0%","No Valid Score"))))
My question is what formula do i use for to get the bonus amount in column G remembering that i need G34 to display No Bonus.
If i use formula =AVERAGE(B30:E30)*F30 i get the correct sum for G30:G34 but it doesn't state "No Bonus" in G34 as required.
PLEASE PLEASE HELP ME I AM GOING OUT OF MY MIND.
I have this aching feeling it is easy but i need fresh eyes.
I have only just learned how to use formulas last week and this just does not come naturally to me.
Thank you in advance for your help/support.
