2003 hide #DIV/0! error with average forumla

[seshultz]

Hello,
I'm new to this forum and searched for similar topics but didn't find an answer I could apply, so sorry if this is redundant.

I am caculating percentages on several sets of columns and calculating the average of another set of columns. My percentage formula is simply: =p11/q11. I used the formula =IF(ISERROR(P11/Q11),"-",P11/Q11) to make the cell display a "-" rather than the #DIV/0! error until data is entered in the cells needed for calculation. When I tried to apply the same formula (in many different iterations), to my average calculation, I keep getting name recognition errors and other types of errors. Here is the average caculation I'm using: =AVERAGE(S8:U8).

Does anyone know how to combine an average calculation with a rule to display a "-" if a #DIV/0! error is created?

Thanks!

 

[Jonmo1]

Welcome to the board...

Try

=IF(ISERROR(AVERAGE(S8:U8)),"-",AVERAGE(S8:U8))

Hope that helps.

 

[Desu Nota from Columbus]

Hello,
I'm new to this forum and searched for similar topics but didn't find an answer I could apply, so sorry if this is redundant.

I am caculating percentages on several sets of columns and calculating the average of another set of columns. My percentage formula is simply: =p11/q11. I used the formula =IF(ISERROR(P11/Q11),"-",P11/Q11) to make the cell display a "-" rather than the #DIV/0! error until data is entered in the cells needed for calculation. When I tried to apply the same formula (in many different iterations), to my average calculation, I keep getting name recognition errors and other types of errors. Here is the average caculation I'm using: =AVERAGE(S8:U8).

Does anyone know how to combine an average calculation with a rule to display a "-" if a #DIV/0! error is created?

Thanks!

Couldn't you just use the IF ISERROR again?

=if(iserror(average(S8:U8)),"-",average(S8:U8))

 

[barry houdini]

In order for the AVERAGE function to produce a value rather than a #DIV/0! error there needs to be at least one number in the range.....so you can use COUNT to check that, i.e.

=IF(COUNT(S8:U8),AVERAGE(S8:U8),"-")

 

[seshultz]

Thanks so much Jonmo! Worked perfectly! Thank you!!!