Too many arguments error

[joey1984]

Hi all,

I have a multiple IFs condition formula which works fine below:

=IF(AND(I200="",J200="",K200=""),"N",IF(AND(G200<today(),h200<>"Complete"),"Forecast End Date is before current date - Status needs to be Complete or Date needs to be updated. Note: Status also cannot be Green",IF(AND(E200<today(),h200="not started"),"start="" date="" past="" current="" -="" either="" modify="" start="" or="" change="" status",if(and(i200="100%,G200">TODAY()),"Status is 100% - Forecast End Date needs to reflect current or earlier date",IF(AND(J200="",OR(H200="Amber",H200="Red")),"Comments cannot be blank if Status is Amber or Red",IF(AND(I200=100%,H200<>"Complete"),"Status is 100% but Status does not display Complete",IF(AND(H200="Complete",I200<>100%),"Status displays Complete but % Complete is not 100%",IF(OR(ISBLANK(B200)=TRUE,ISBLANK(C200)=TRUE,ISBLANK(D200)=TRUE,ISBLANK(E200)=TRUE,ISBLANK(F200)=TRUE,ISBLANK(G200)=TRUE),"One of the Cells needs to be filled",IF(OR(ISNUMBER(DAY(E200))=FALSE,ISNUMBER(DAY(G200))=FALSE,G200>=E200),"N","Start Date is later than the Forecast End Date OR there is an invalid date - Modify either Start or Forecast End Date")))))))))

However when I try to insert the below working formula also to the above formula, I get a Too many arguments error. Can someone please advise how to fix this?

=IF(AND(G200-F200>60,H200="Green"),"Forecast End Date is two months or greater from the Baseline End Date, Status cannot be Green","")</today(),h200="not></today(),h200<>

 

[jtakw]

Hi,

Don't have any of your data to test, and not sure of your flow of logic, but try this:

=IF(AND(I200="",J200="",K200=""),"N",IF(AND(G200="Complete"),"Forecast End Date is before current date - Status needs to be Complete or Date needs to be updated. Note: Status also cannot be Green",IF(AND(E200=TODAY()),"Status is 100% - Forecast End Date needs to reflect current or earlier date",IF(AND(J200="",OR(H200="Amber",H200="Red")),"Comments cannot be blank if Status is Amber or Red",IF(AND(I200=100%,H200<>"Complete"),"Status is 100% but Status does not display Complete",IF(AND(H200="Complete",I200<>100%),"Status displays Complete but % Complete is not 100%",IF(OR(ISBLANK(B200),ISBLANK(C200),ISBLANK(D200),ISBLANK(E200),ISBLANK(F200),ISBLANK(G200)),"One of the Cells needs to be filled",IF(OR(ISNUMBER(DAY(E200))=FALSE,ISNUMBER(DAY(G200))=FALSE,G200>=E200),"N",IF(AND(G200-F200>60,H200="Green"),"Forecast End Date is two months or greater from the Baseline End Date, Status cannot be Green","Start Date is later than the Forecast End Date OR there is an invalid date - Modify either Start or Forecast End Date")))))))))

I do have a suggestion, instead of cluttering the formula like this, why not put all your text comments in separate cells in a single column, like Z1, Z2, Z3, etc., then just refer to the cell accordingly in your formula.
You can hide Column Z so it won't be seen.

 

[joey1984]

Hi, no luck it comes back with N when it should come back with that comment Forecast End Date is two months or greater from the Baseline End Date, Status cannot be Green. Is there any way I can attach the file?

 

[LockeGarmin]

I might be able to help by giving you one of my favorite excel tips:

Your formula would be easier to maintain and edit if you put line breaks in front of each IF() since they are nested. You can do so by pressing Alt+Enter when typing the formula.

=IF(AND(I200="",J200="",K200=""),"N",IF(AND(G200="Complete"),"Forecast End Date is before current date - Status needs to be Complete or Date needs to be updated. Note: Status also cannot be Green",
IF(AND(E200=TODAY()),"Status is 100% - Forecast End Date needs to reflect current or earlier date",
IF(AND(J200="",OR(H200="Amber",H200="Red")),"Comments cannot be blank if Status is Amber or Red",
IF(AND(I200=100%,H200<>"Complete"),"Status is 100% but Status does not display Complete",
IF(AND(H200="Complete",I200<>100%),"Status displays Complete but % Complete is not 100%",
IF(OR(ISBLANK(B200)=TRUE,ISBLANK(C200)=TRUE,ISBLANK(D200)=TRUE,ISBLANK(E200)=TRUE,ISBLANK(F200)=TRUE,ISBLANK(G200)=TRUE ),"One of the Cells needs to be filled",
IF(OR(ISNUMBER(DAY(E200))=FALSE,ISNUMBER(DAY(G200))=FALSE,G200>=E200),"N",
"Start Date is later than the Forecast End Date OR there is an invalid date - Modify either Start or Forecast End Date"))))))))

Also, you can't add your new if statement as is because it has both a true and a false statement and your larger if statement already has the trues' and false statements. just remove the ending false parameter from your new if statement and stick it somewhere in the middle of your IFs.

[COLOR=#574123]IF(AND(G200-F200>60,H200="Green"),"Forecast End Date is two months or greater from the Baseline End Date, Status cannot be Green",[/COLOR]

 

[jtakw]

Try this one:

=IF(AND(I200="",J200="",K200=""),"N",IF(AND(G200="Complete"),"Forecast End Date is before current date - Status needs to be Complete or Date needs to be updated. Note: Status also cannot be Green",IF(AND(E200=TODAY()),"Status is 100% - Forecast End Date needs to reflect current or earlier date",IF(AND(J200="",OR(H200="Amber",H200="Red")),"Comments cannot be blank if Status is Amber or Red",IF(AND(I200=100%,H200<>"Complete"),"Status is 100% but Status does not display Complete",IF(AND(H200="Complete",I200<>100%),"Status displays Complete but % Complete is not 100%",IF(OR(ISBLANK(B200),ISBLANK(C200),ISBLANK(D200),ISBLANK(E200),ISBLANK(F200),ISBLANK(G200)),"One of the Cells needs to be filled",IF(AND(G200-F200>60,H200="Green"),"Forecast End Date is two months or greater from the Baseline End Date, Status cannot be Green",IF(OR(ISNUMBER(DAY(E200))=FALSE,ISNUMBER(DAY(G200))=FALSE,G200>=E200),"N","Start Date is later than the Forecast End Date OR there is an invalid date - Modify either Start or Forecast End Date")))))))))

Question, all the references in your formula where If H200="Amber", H200="Red", H200="Green"...Do the cells actually contain the TEXT "Amber", "Red", or "Green"?

 

[FDibbins]

A few observations...
1. You don't need that 2nd and 3rd ANDs, you only have 1 test...also, you left out the = in both
2. When using ISBLANK, you don't need the =TRUE, in fact if texting for an empty cell, you can just use =""
3. That entire ISBLANK() set can be replaced with IF(counta(B200:G200)=0
4. what are you trying to test with the ISNUMBER(DAY(E200)) etc? Unless you might have text in E200, it will always return TRUE, even on a blank cell
5. I agree with the observation on referencing cells for the test

 

[FDibbins]

This is what I shortened it to...
=IF(AND(I200="",J200="",K200=""),"N",IF(G200="Complete","Forecast blah blah",IF(E200=TODAY(),"Status blah blah",IF(AND(J200="",OR(H200="Amber",H200="Red")),"Comments cannot blah blah",IF(AND(I200=100%,H200<>"Complete"),"Status is blah blah",IF(AND(H200="Complete",I200<>100%),"Status blah blah",IF(COUNTA(B200:G200)=0,"One of the blah blah",IF(OR(ISNUMBER(DAY(E200))=FALSE,ISNUMBER(DAY(G200))=FALSE,G200>=E200),"N","Start Date blah blah"))))))))