Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with ISERROR
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,FAL SE),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,FAL SE),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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with ISERROR
juliejg1 wrote...
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)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with ISERROR
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!N1 8=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,FAL SE),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,FAL SE),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,FAL SE),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,FAL SE),0))/30*(AB18/Data!O18)),0)) Laura "juliejg1" wrote in message ... 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,FAL SE),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,FAL SE),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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use of ISERROR | Excel Worksheet Functions | |||
Iserror | Excel Discussion (Misc queries) | |||
iserror | Excel Worksheet Functions | |||
Iserror & VBA | Excel Discussion (Misc queries) | |||
iserror | Excel Discussion (Misc queries) |