IS ERR Function Help

[montanaaggie]

I have values in column E and F. In G I have an equation of F/E. However I will now and then expect to receive the value #Div/0!. How can I create an IF statement that will create the calculation f/e, however if an error is found it will display 0 or "-"

 

[T. Valko]

I have values in column E and F. In G I have an equation of F/E. However I will now and then expect to receive the value #Div/0!. How can I create an IF statement that will create the calculation f/e, however if an error is found it will display 0 or "-"

One way...

For a return of 0:

=IF(E1=0,0,F1/E1)

 

[montanaaggie]

That is one way, however there will be times depending on the data that its not due to the fact there is a zero. I know there is a way to do an if statement combined with iserror or iserr, just can't seem to get my logic to work.

 

[vikingrob]

=IF(ISERR(F1/E1),0,F1/E1)

If E1 is 0, then ISERR(F1/E1) returns the logical value TRUE. The IF function calls for the first argument to be something that evaluates to either TRUE or FALSE.

 

[barry houdini]

I can't see many (any?) circumstances where Biff's suggestion won't work but a simple ISERR version would go something like this

=IF(ISERR(F1/E1),0,F1:E1)

 

[Greg Truby]

Do note that there is a difference downstream between using a 0 and a dash for certain functions. Note the difference in the results of AVERAGE() and COUNT() if using Biff's solution with a zero or with a Dash.

Excel Workbook
	E	F	G	H
1	5	3	0.6	0.6
2	32		0	0
3	2	32	16	16
4		23	0	-
5	0	34	0	-
6		Average	3.32	5.533333
7		Count	5	3
Sheet1

 

[Aladin Akyurek]

That is one way, however there will be times depending on the data that its not due to the fact there is a zero. I know there is a way to do an if statement combined with iserror or iserr, just can't seem to get my logic to work.

Since we are expecting a number, we can also invoke ISNUMBER which reports FALSE for anything non-numeric, including Excel's error values:

=IF(ISNUMBER(F1/E1),F1/E1,0)

 

[T. Valko]

Since we are expecting a number, we can also invoke ISNUMBER which reports FALSE for anything non-numeric, including Excel's error values:

=IF(ISNUMBER(F1/E1),F1/E1,0)

A few keystrokes shorter:

=IF(COUNT(F1/E1),F1/E1,0)