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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jignesh - 31 Aug 2008 00:15 GMT

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?

Signature

Jignesh, JP

Reply to this Message

Peo Sjoblom - 31 Aug 2008 00:21 GMT

Try

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

CSE entered

Signature

Regards,

Peo Sjoblom

>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
[quoted text clipped - 5 lines]
> 30-69, i am getting #DIV/0. How do i get 0 instead of #DIV/0.
> Can amy one help me?

Reply to this Message

Jignesh - 31 Aug 2008 00:44 GMT

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

Signature

Jignesh, JP

> Try
>
[quoted text clipped - 11 lines]
> > 30-69, i am getting #DIV/0. How do i get 0 instead of #DIV/0.
> > Can amy one help me?

Reply to this Message

smartin - 31 Aug 2008 00:36 GMT

> 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
[quoted text clipped - 3 lines]
> 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")))

Reply to this Message

smartin - 31 Aug 2008 01:40 GMT

>> 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
[quoted text clipped - 10 lines]
>
> =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.

Reply to this Message

T. Valko - 31 Aug 2008 03:14 GMT

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

Signature

Biff
Microsoft Excel MVP

>>> 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
[quoted text clipped - 11 lines]
>> =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.

Reply to this Message

smartin - 31 Aug 2008 03:59 GMT

>> Wow, what a stupid moment for me! Please ignore my post.
>
[quoted text clipped - 6 lines]
> 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!

Reply to this Message

Jignesh - 31 Aug 2008 21:41 GMT

it worked, thank you.

Signature

Jignesh, JP

> > 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
[quoted text clipped - 9 lines]
>
> =IF(ISERROR(AVERAGE(IF(F30:F69=3,O30:O69,"0")),0,AVERAGE(IF(F30:F69=3,O30:O69,"0")))

Reply to this Message