Working with Logical Functions

[Chinny]

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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee
(columnA29)[/TD]
[TD]Week 1
(columnB29)[/TD]
[TD]Week 2
(columC29)[/TD]
[TD]Week 3
(columD29)[/TD]
[TD]Week 4
(columE29)[/TD]
[TD]Bonus
(columF29)[/TD]
[TD]Bonus Amount
(columG29)[/TD]
[/TR]
[TR]
[TD]Kina
(columnA30)[/TD]
[TD]$2,000.00[/TD]
[TD]$2,000.00[/TD]
[TD]$4,000.00[/TD]
[TD]$3,333.00[/TD]
[TD]7%[/TD]
[TD]$198.33[/TD]
[/TR]
[TR]
[TD]Bina
(columnA31)[/TD]
[TD]$1,000.00[/TD]
[TD]$2,345.00[/TD]
[TD]$2,000.00[/TD]
[TD]$2,121.00[/TD]
[TD]3%[/TD]
[TD]$56.00[/TD]
[/TR]
[TR]
[TD]Tina
(columnA32)[/TD]
[TD]$2,500.00[/TD]
[TD]$2,000.00[/TD]
[TD]$2,345.00[/TD]
[TD]$1,000.00[/TD]
[TD]3%[/TD]
[TD]$58.84[/TD]
[/TR]
[TR]
[TD]Lina
(columnA33)[/TD]
[TD]$4,000.00[/TD]
[TD]$3,214.00[/TD]
[TD]$2,000.00[/TD]
[TD]$4,000.00[/TD]
[TD]10%[/TD]
[TD]$330.35[/TD]
[/TR]
[TR]
[TD]Gina
(columnA34)[/TD]
[TD]$800.00[/TD]
[TD]$1,800.00[/TD]
[TD]$1,000.00[/TD]
[TD]$1,500.00[/TD]
[TD]0%[/TD]
[TD]No Bonus[/TD]
[/TR]
</tbody>[/TABLE]


















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.

 

[jono19872006]

cant you just do

IF(F30=0,"No Bonus",F23*(AVERAGE(B30:E30)))

 

[Chinny]

You are a life saver, I changed the =0 for >1500 and it worked perfectly.
You have saved me from a long long night..

Thank you