Problem with average working out

[knockout]

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

<tbody>
</tbody><colgroup><col></colgroup>

 

[arthurbr]

I don't understand exactly but your formula in K17 and K18 return text, not numbers. Remove the double quotes and try again

 

[palaeontology]

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

 

[SpillerBD]

averaging text...?
You can only average numbers and without any numbers you are always diving by zero.

 

[knockout]

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.

 

[knockout]

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%)

 

[HHoward1984]

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

 

[knockout]

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