averaging numbers from IF statements

[Munkykween]

I have a column of IF statements which produce a number depending upon the percentage that is entered into the column next to it. How do I have the column of IF statements average all of the numbers that are produced (out by a decimal and two places)? Each time that I try it gives me this: #DIV/0!

The IF statement is this: =IF(C9>89%,"4",IF(C9>79%,"3",IF(C9>69%,"2",IF(C9>59%,"1"))))

So it looks like this:

73% 2
68% 1
85% 3
#DIV/0!


I need #DIV/0! to have the average (which would be 2.00 in this case)

Thank you for your help this one is REALLY stumping me

 

[Aladin Akyurek]

Remove the double quotes from 4, 3, 2, and 1.

 

[facethegod]

Remove the quotes from your numbers inside your if formula, then try your average formula

=IF(C9>89%,4,IF(C9>79%,3,IF(C9>69%,2,IF(C9>59%,1))))

HTH

 

[dave3009]

I think there's a slicker way with them crazy {} things and and combination of ; and , . I've seen it and I think it may have been Brian from Maui that posted the solution. Looked really cool and much better than a nested =IF

Hopefully someone can remember the post or suggest the same

 

[dave3009]

My mistake it was HOTPEPPER

http://www.mrexcel.com/board2/viewtopic.php?p=1349925&highlight=#1349925

 

[Munkykween]

I tried removing the quotes and HOTPEPPER's formula and either way all I get is ###. Moving the cursor over that cell shows 300% (I don't know why that is). Any other suggestions?

 

[dave3009]

Did the other suggestions work. I have no intention of stepping on Aladin Akyureks toes?

 

[Munkykween]

This is weird. The first time that I tried removing the quotes it didn't work, but now it does.

Thanks so much for all of your help!!!! I am ecstatic with the results!!