Help with ISERROR

[juliejg1]

I have the following formula in a cell which works correctly:

=IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,MIN(Data!N18-M18,DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(AB18/Data!O18)-MIN(Data!N18-M18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30*(AB18/Data!O18),MIN(Data!M18-N18,DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(-AB18/Data!O18)+MIN(Data!M18-N18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30*(AB18/Data!O18)),0)

I need to add an error check because I have getting #DIV/0 in blank cells.
I need it to check for error message and put "-" in those cells where an
error occurs. I have tried to add the ISERROR function to the formula but
must be doing it incorrectly because I can't get it to work. Any suggestions?

 

[Harlan Grove]

I have the following formula in a cell which works correctly:

=IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,MIN(Data!N18-M18,
DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(AB18/Data!O18)
-MIN(Data!N18-M18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,
FALSE),0))/30*(AB18/Data!O18),MIN(Data!M18-N18,DAYS360(Data!I18,
Scorecard!$B$4,FALSE))/30*(-AB18/Data!O18)+MIN(Data!M18-N18,
MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30
*(AB18/Data!O18)),0)

I need to add an error check because I have getting #DIV/0 in blank
cells. I need it to check for error message and put "-" in those
cells where an error occurs. . . .

....

Do you mean the formula works correctly when there are no blank cells,
but gives a #DIV/0! error when there are blank cells? If so, the only
cell that could cause the #DIV/0! error when blank is Data!O18. If you
want the result to be "-" when Data!O18 is blank, try

=IF(ISBLANK(Data!O18),"-",your_original_formula_here)

It also appears you could simplify your formula. The 3rd argument to
DAYS360 defaults to FALSE, so it could be omitted. Doing so and moving
the unary minus around gives

=IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,
MIN(Data!N18-M18,DAYS360(Data!I18,Scorecard!$B$4))/30*(AB18/Data!O18)
-MIN(Data!N18-M18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4),0))/30
*(AB18/Data!O18),
-MIN(Data!M18-N18,DAYS360(Data!I18,Scorecard!$B$4))/30*(AB18/Data!O18)
+MIN(Data!M18-N18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4),0))/30
*(AB18/Data!O18)),0)

Note that every term in the inner IF call includes /30*(AB18/Data!
O18), so move it outside the IF call.

=IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,
MIN(Data!N18-M18,DAYS360(Data!I18,Scorecard!$B$4))
-MIN(Data!N18-M18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4),0)),
-MIN(Data!M18-N18,DAYS360(Data!I18,Scorecard!$B$4))
+MIN(Data!M18-N18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4),0)))
/30*(AB18/Data!O18),0)

Then note that the 2nd and 3rd arguments of the inner IF call are
mostly the same, so the common parts could be moved outside the IF
call.

=IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,1,-1)
*(MIN(IF(Data!N18>=M18,Data!N18-M18,Data!M18-N18),
DAYS360(Data!I18,Scorecard!$B$4))
-MIN(IF(Data!N18>=M18,Data!N18-M18,Data!M18-N18),
MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4),0)))
/30*(AB18/Data!O18),0)

Then add checking whether Data!O18 is blank.

=IF(ISBLANK(Data!O18),"-",
IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,1,-1)
*(MIN(IF(Data!N18>=M18,Data!N18-M18,Data!M18-N18),
DAYS360(Data!I18,Scorecard!$B$4))
-MIN(IF(Data!N18>=M18,Data!N18-M18,Data!M18-N18),
MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4),0)))
/30*(AB18/Data!O18),0))

 

[Laura Cook]

I did not test this, but you should be able to just add another IF statement
and check for errors:

=IF(ISERROR(IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,MIN(Data!N18-M18,DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(AB18/Data!O18)-MIN(Data!N18-M18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30*(AB18/Data!O18),MIN(Data!M18-N18,DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(-AB18/Data!O18)+MIN(Data!M18-N18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30*(AB18/Data!O18)),0)),"-",IF(Scorecard!$B$4>=Data!I18,IF(Data!N18>=M18,MIN(Data!N18-M18,DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(AB18/Data!O18)-MIN(Data!N18-M18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30*(AB18/Data!O18),MIN(Data!M18-N18,DAYS360(Data!I18,Scorecard!$B$4,FALSE))/30*(-AB18/Data!O18)+MIN(Data!M18-N18,MAX(DAYS360(Data!I18+O18*30,Scorecard!$B$4,FALSE),0))/30*(AB18/Data!O18)),0))

Laura