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

 

[Jignesh]

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

 

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

 

[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")))

 

[mikerickson]

=AVERAGE((F30:F69=3)*O30:O69)
is another CSE formulation that will work