averaging numbers from IF statements

Munkykween

New Member
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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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

facethegod

Well-known Member
Remove the quotes from your numbers inside your if formula, then try your average formula

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

HTH

dave3009

Well-known Member
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

Munkykween

New Member
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

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

Munkykween

New Member
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!!

Replies
3
Views
812
Replies
2
Views
300
Replies
1
Views
298
Replies
4
Views
289
Replies
4
Views
423

1,191,196
Messages
5,985,229
Members
439,950
Latest member
Xearo96

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back