How to avoid #DIV/0, I AM USING =AVERAGE(IF(F30:F69=3,O30:O69,"0")

[Jignesh]

I am using =AVERAGE(IF(F30:F69=3,O30:O69,"0")). Also using CSE to {}.
In Column F30 to F69 i have value 1-10. Some cells are blanks. In Column O30
to O69 I have a calcuation based on Column L and N. When there is a Number 3
in Column F, raw30-69 and some value in Column O raw 30-69, I am getting
average of those value, but when number 3 is not present in Column F raw
30-69, i am getting #DIV/0. How do i get 0 instead of #DIV/0.
Can amy one help me?

 

[Peo Sjoblom]

Try

=IF(COUNTIF(F30:F69,3)=0,0,AVERAGE(IF(F30:F69=3,O30:O69)))


CSE entered


--


Regards,


Peo Sjoblom

 

[smartin]

I am using =AVERAGE(IF(F30:F69=3,O30:O69,"0")). Also using CSE to {}.
In Column F30 to F69 i have value 1-10. Some cells are blanks. In Column O30
to O69 I have a calcuation based on Column L and N. When there is a Number 3
in Column F, raw30-69 and some value in Column O raw 30-69, I am getting
average of those value, but when number 3 is not present in Column F raw
30-69, i am getting #DIV/0. How do i get 0 instead of #DIV/0.
Can amy one help me?

Jignesh,

Wrap the function with an error trap, like

=IF(ISERROR(AVERAGE(IF(F30:F69=3,O30:O69,"0")),0,AVERAGE(IF(F30:F69=3,O30:O69,"0")))

 

[Jignesh]

It is working, you are great, God bless you sir. Thank you,

 

[smartin]

Jignesh,

Wrap the function with an error trap, like

=IF(ISERROR(AVERAGE(IF(F30:F69=3,O30:O69,"0")),0,AVERAGE(IF(F30:F69=3,O30:O69,"0")))

Wow, what a stupid moment for me! Please ignore my post.

 

[T. Valko]

Wow, what a stupid moment for me! Please ignore my post.

Why? There is a missing closing ) but other than that the formula will do
what was asked.

Is it the best solution? Probably not but it does work which is half the
battle!

Get the correct result first by any means available then optimize if
possible!

 

[smartin]

Why? There is a missing closing ) but other than that the formula will do
what was asked.

Is it the best solution? Probably not but it does work which is half the
battle!

Get the correct result first by any means available then optimize if
possible!

I posted without testing, then thought I was totally off, again without
testing. Not very "smartin", eh?

Ah, well, I hope we are all allowed these moments...

Thanks for the vote of confidence, Biff!

 

[Jignesh]

it worked, thank you.
--
Jignesh, JP

Jignesh,

Wrap the function with an error trap, like

=IF(ISERROR(AVERAGE(IF(F30:F69=3,O30:O69,"0")),0,AVERAGE(IF(F30:F69=3,O30:O69,"0")))