Nested IF AND Excel Formula

[lashears]

Hi everyone,

need some urgent help.

I have a nested excel formula that keeps giving me the error that I'm missing a parenthesis, but when I fix what I believe is missing then it throws the whole formula into error.

Basically my nested IF)AND) nested formula is as follows:



=IF(Y4<>"Yes",0,if(S4<DATEVALUE("1/31/2021"),210,If(and(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021"),175,If(and(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021"),140,If(and(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021"),105,If(and(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021"),70,If(and(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021"),35)





can anyone tell me why I keep receive the "missing parenthesis" error?



thanks for your help!

 

[etaf]

writing out like this often helpd
if(S4<DATEVALUE("1/31/2021"),210,

If(and(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021"),175,

If(and(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021"),140,

If(and(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021"),105,

If(and(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021"),70,

If(and(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021"),35

)

You dont have a FALSE at the end
But you are also missing the closing brackets for all the AND statements

if(S4<DATEVALUE("1/31/2021"),210,

If(and(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021")),175,

If(and(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021")),140,

If(and(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021")),105,

If(and(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021")),70,

If(and(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021")),35, "what if ALL false"
)

 

[jtakw]

Hi,

You're missing Closing brackets for all your AND functions.
Also, what's in S4, just a date?
We can probably shorten this formula a bit, but here it is fixed.

=IF(Y4<>"Yes",0,IF(S4<DATEVALUE("1/31/2021"),210,IF(AND(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021")),175,IF(AND(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021")),140,IF(AND(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021")),105,IF(AND(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021")),70,IF(AND(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021")),35)))))))

 

[jtakw]

So assuming S4 contains a Date only, see if this Much shorter formula does what you need:

=IFERROR(IF(Y4<>"Yes",0,IF(YEAR(S4)=2021,CHOOSE(MONTH(S4),210,175,140,105,70,35),"")),"")

 

[jtakw]

Just noticed at the End of your long formula, for the month of June, you have 6/2/21 to 6/11/21, is that correct, or a typo?
So 6/1/21 & 6/12 to 6/30/21 is Not to be included?

 

[lashears]

So assuming S4 contains a Date only, see if this Much shorter formula does what you need:

=IFERROR(IF(Y4<>"Yes",0,IF(YEAR(S4)=2021,CHOOSE(MONTH(S4),210,175,140,105,70,35),"")),"")

Thank you so much!! I was working for hours with different formula combinations trying to find a solution. You are awesome!!

 

[lashears]

writing out like this often helpd
if(S4<DATEVALUE("1/31/2021"),210,

If(and(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021"),175,

If(and(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021"),140,

If(and(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021"),105,

If(and(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021"),70,

If(and(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021"),35

)

You dont have a FALSE at the end
But you are also missing the closing brackets for all the AND statements

if(S4<DATEVALUE("1/31/2021"),210,

If(and(S4>=DATEVALUE("2/1/2021"),S4<=DATEVALUE("2/28/2021")),175,

If(and(S4>=DATEVALUE("3/1/2021"),S4<=DATEVALUE("3/31/2021")),140,

If(and(S4>=DATEVALUE("4/1/2021"),S4<=DATEVALUE("4/30/2021")),105,

If(and(S4>=DATEVALUE("5/1/2021"),S4<=DATEVALUE("5/31/2021")),70,

If(and(S4>=DATEVALUE("6/2/2021"),S4<=DATEVALUE("6/11/2021")),35, "what if ALL false"
)

I used the solution jatw posted, but thank you so much for responding as well, it's much appreciated!

 

[etaf]

you are welcome, thanks for responding , appreciated

 

[jtakw]

Thank you so much!! I was working for hours with different formula combinations trying to find a solution. You are awesome!!

You're very welcome, thanks for the feedback.
Don't forget to mark the post that solved your problem as solution.

 

[lashears]

Just noticed at the End of your long formula, for the month of June, you have 6/2/21 to 6/11/21, is that correct, or a typo?
So 6/1/21 & 6/12 to 6/30/21 is Not to be included?

That is correct, it has to do with reimbursement dates, and those dates do not receive a reimbursement