Getting an average for cells containing formula

[Faded06]

Hello,

I have cells C5:C36 that contain a series of IF formulas (=IF(B5="L4(+)","95%",IF(B5="L4","85%",IF(B5="L4(-)","80%",IF(B5="L3(+)","79%",IF(B5="L3","75%",IF(B5="L3(-)","70%",IF(B5="L2(+)","69%",IF(B5="L2","65%",IF(B5="L2(-)","60%",))))))))) that reference the adjacent cells B5:B36. When I enter the desired text in the B column cells it returns the desired value in the C column just fine.

My issue is I now want to be able to average the values in the C columns (which are all percentages). However, when I use the Average function for the percentage value in the C column, it returns a Div/0 error.

I'm assuming that the Average function is reading the cells in column C as blank and so it returns the Div/0 error.
I've resorted to retyping the percentage values in the C column into the adjacent D column and then averaging those percents. This of course is redundant and time consuming.

I'd like a solution that would just average the returned percentage value in the C column.

 

[etaf]

are they actually % or TEXT
"95%" is not a % is text and so that maybe the issue

is the column formated as text or % ]if % then
change to the % value ie

=IF(B5="L4(+)",0.95,IF(B5="L4",0.85,IF(B5="L4(-)",0.8,IF(B5="L3(+)",0.79,......etc

 

[Faded06]

Wow! Thank you so much. Such a simple solution and it works perfectly!

 

[etaf]

you are welcome