Problem with average working out

knockout

New Member
Joined
Jan 2, 2018
Messages
4
Hello,

I am trying to work out an average of 2 cells however the 2 cells have a IF formula which is the selected from another cell.

so on my page I have a dropdown option on 2 cells F17 & F18 (options = 3, 2, 1, N/A)
Cells K17 & K18 have the formula =IF(F17=3, "100%",IF(F17=2, "66%",IF(F17=1, "33%",IF(F17=0, "0%",IF(F17="N/A", "100%")))))

I want the average of the cells F17 & F18

However when I use =Average formula I get #DIV/0!

Any help would be awesome.

Thank you
[TABLE="width: 73"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
I don't understand exactly but your formula in K17 and K18 return text, not numbers. Remove the double quotes and try again
 
Upvote 0
your '100%', '66%' etc are text, not numbers, so you're trying to average text, which of course won't work.

Perhaps change '100%' to 100, '66%' to 66 etc etc etc
 
Upvote 0
averaging text...?
You can only average numbers and without any numbers you are always diving by zero.
 
Upvote 0
Try without '%' within the formula and still got the same problem. just to add I have other formulas which refer to '%' ok so this is why I didn't try this in the first place.
 
Upvote 0
Further down in my workbook I have a less complicated formula which is using the IF and OR formula along with '%' and the averaging is still working. is it because I'm using a less complicated formula with a combination as well

=IF(OR(F20=1,F20="N/A"),100%,0%)
 
Upvote 0
the problem isn't with your % it is with the " ". When you write this you are effectively telling Excel the product is a text and not a number. try this instead

=IF(F17=3, 100%,IF(F17=2, 66%,IF(F17=1, 33%,IF(F17=0, 0%,IF(F17="N/A", 100%)))))

your formula should work fine but another way of doing it would be

=IF(F17=3,1,IF(F17=2,0.66,IF(F17=1,0.33,1)))

You would then need to format your cells a %

good luck


 
Upvote 0
Spot on HHoward1984, Thank you for that. Sorry guys very novice to Excel and learning on the job at the moment, not so much actually in my job description just something set by my boss so thanks again!!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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?

Disable AdBlock

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

Disable AdBlock Plus

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
Back
Top