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, JP

Try

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


CSE entered


--


Regards,


Peo Sjoblom



"Jignesh" wrote in message
...
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, JP

Jignesh wrote:
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,A VERAGE(IF(F30:F69=3,O30:O69,"0")))

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


"Peo Sjoblom" wrote:

Try

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


CSE entered


--


Regards,


Peo Sjoblom



"Jignesh" wrote in message
...
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, JP

smartin wrote:
Jignesh wrote:
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,A VERAGE(IF(F30:F69=3,O30:O69,"0")))

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

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!

--
Biff
Microsoft Excel MVP


"smartin" wrote in message
...
smartin wrote:
Jignesh wrote:
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,A VERAGE(IF(F30:F69=3,O30:O69,"0")))
Wow, what a stupid moment for me! Please ignore my post.

T. Valko wrote:
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!


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!

it worked, thank you.
--
Jignesh, JP


"smartin" wrote:

Jignesh wrote:
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,A VERAGE(IF(F30:F69=3,O30:O69,"0")))