Create a multiple nested IF statements

[Chinny]

Hi there,

I am new to this forum (1st Post) and also new to excel.

I am currently doing my bookkeeping course and i am stuck on a question and need some help.

The exercise asks me to 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.

This is what is should look like when finished.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Week 1[/TD]
[TD]Week 2[/TD]
[TD]Week 3[/TD]
[TD]Week 4[/TD]
[TD]Bonus (%)[/TD]
[TD]Bonus Amount[/TD]
[/TR]
[TR]
[TD]Kina[/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[/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[/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[/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[/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]

Can anyone please me what formula i would use if column F line 2 to get that answer.

PLEASE PLEASE PLEASE

 

[oldbrewer]

taking the first row you have to average the figures for weeks 1 to 4 this is 11333/4
bonus paid was 198.33 so percentage is (198.33 * 4 *100) / 11333 =7

I don't see why or even how nested IF statements are appropriate

is there more information such as if no sales in any one week no bonus is payable?

 

[Chinny]

taking the first row you have to average the figures for weeks 1 to 4 this is 11333/4
bonus paid was 198.33 so percentage is (198.33 * 4 *100) / 11333 =7

I don't see why or even how nested IF statements are appropriate

is there more information such as if no sales in any one week no bonus is payable?

That's all the information provided but I some how did this and it works.
I hope its what they wanted because I have no idea lol

=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"))))

Thank you

 

[oldbrewer]

in your post you did not mention that the bonus rate was set by sales levels - see my post 2

as it is, your answer is correct 10/10 or A+

 

[Chinny]

I am so sorry, I didn't realise I missed that bit.