iferror

[Roy Gudgeon]

Hi Guys

I am currently using office 2007
I have a large spreadsheet that uses the following formula:

=IF(G5/5>1,F5/g5,g5/f5)

This produces a "div/0" error from time to time when the values in G5 & F5
are 0
I have been trying to use iferror to resolve this to report 0 but keep
getting an error message suggesting either a problem with the formula or not
enough arguments etc....

Any suggestions as to the correct formula ?

thanks
Roy

 

[John C]

Your error will only ever occur if F5=0, so you could use the nested IF like
this:
=IF(G5/5>1,F5/G5,IF(F5=0,"ERROR",G5/F5))
You can replace the "ERROR" to "" or whatever other value you want to display.
This also assumes that F5 and G5 are populated with numbers.

 

[Infinitogool]

Hi Roy
Try

=IF(F5*G5=0,"value 0",IF(G5/5>1,F5/G5,G5/F5))

Regards,
Pedro J.

 

[Lars-Åke Aspelin]

Hi Guys

I am currently using office 2007
I have a large spreadsheet that uses the following formula:

=IF(G5/5>1,F5/g5,g5/f5)

This produces a "div/0" error from time to time when the values in G5 & F5
are 0
I have been trying to use iferror to resolve this to report 0 but keep
getting an error message suggesting either a problem with the formula or not
enough arguments etc....

Any suggestions as to the correct formula ?

thanks
Roy

Try this:

=IF(G5>5,F5/G5,IF(F5=0,0,G5/F5))

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Pedro, your formula does not fly.

F5*G5=0 is NOT the same as AND(F5=0,G5=0)

Check with F5=0 and G5=4

Lars-Åke

 

[Roy Gudgeon]

thanks for replies
but am still having probs showing 0 rather than DIV/0

 

[John C]

Actually, since the OP said he wanted a 0 instead of a DIV/0 error posted,
his formula works just fine.

 

[John C]

Give us the values for the cells in columns F & G that are still generating a
DIV/0 error.

 

[Lars-Åke Aspelin]

thanks for replies
but am still having probs showing 0 rather than DIV/0

But you wrote "I have been trying to .... resolve this to report 0".

That gave me the impression that you acually wanted to
have 0 as the result when both F5 and G5 are 0.

What result do you want in that case?

Try this:

=IF(AND(F5=0,G5=0), state here what you want, IF(G5>5,F5/G5,G5/F5))

if you want DIV/0 error, this will give one

=IF(AND(F5=0,G5=0), 1/0, IF(G5>5, F5/G5, G5/F5))

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Actually, since the OP said he wanted a 0 instead of a DIV/0 error posted,
his formula works just fine.

I am sorry, I see that now.

Lars-Åke

 

[T. Valko]

Try this:

=IF(G5/5>1,F5/G5,IFERROR(G5/F5,0))

 

[Roy Gudgeon]

Thanks T Valko but it still returns the Div error.

Lars, John C, I want to report 0 when both g5 & f 5 are zero.
thanks for continued help

 

[Roy Gudgeon]

Pedro

your formula does fly and I ahve my solution
thaks very much for your help and to all other respondees

 

[T. Valko]

=IF(G5/5>1,F5/G5,IFERROR(G5/F5,0))

it still returns the Div error.

If both cells are empty or 0 the result is 0. If either cell is empty or 0
the result is 0. The only way that formula could return #DIV/0! is if G5
already has a #DIV/0! error.

Try this:

=IFERROR(IF(G5/5>1,F5/G5,G5/F5),0)