Nested if statement

[ariel20029]

Hi ,
I am getting an error message in excel with this formula. Can you please help.


1st its looking to see if it is a duplicate row and returning 0 if so
2 its looking to see what quarter the order falls in and if the end date is after the quarter then return 100000 else 0.
thanks,
Sharon


=IF(AS31="DUP",0,IF(BB31>=1,0,IF(AND(C31="2014-Q1",R31>= DATEVALUE("4/1/2015"),100000,IF(AND(C31="2014-Q2",R31>=DATEVALUE("7/1/2015"),100000,IF(AND(C31="2014-Q3",R31>=DATEVALUE("10/1/2015"),100000,IF(AND(C31="2014-Q4",R31<DATEVALUE("1/1/2016"),100000,0))))))

thanks,
Sharon

 

[mkmohsinali]

Hi ariel20029
Can you attach excel file?

 

[ariel20029]

Im sorry how do you attach an excel file?
Sharon

 

[Caribeiro77]

your formula is incomplete but in what is possible to see in your post, you might have to hange some parenthesis, something like:

=IF(AS31="DUP",0,IF(BB31>=1,0,IF(AND(C31="2014-Q1",R31>= DATEVALUE(4/1/2015)),100000,IF(AND(C31="2014-Q2",R31>=DATEVALUE(7/1/2015)),100000,IF(AND(C31="2014-Q3",R31>=DATEVALUE(10/1/2015)),100000,IF(AND(C31="2014-Q4",R31<datevalue("1 1="" 2016"),100000,0))))))
</datevalue("1>

 

[ariel20029]

your formula is incomplete but in what is possible to see in your post, you might have to hange some parenthesis, something like:

=IF(AS31="DUP",0,IF(BB31>=1,0,IF(AND(C31="2014-Q1",R31>= DATEVALUE(4/1/2015)),100000,IF(AND(C31="2014-Q2",R31>=DATEVALUE(7/1/2015)),100000,IF(AND(C31="2014-Q3",R31>=DATEVALUE(10/1/2015)),100000,IF(AND(C31="2014-Q4",R31<datevalue("1 1="" 2016"),100000,0))))))
</datevalue("1>

Hi, I made the change you recommended, but Im still not getting a value returned.

=IF(AS2="DUP",0,IF(BB2>=1,0,IF(AND(C2="2014-Q1",R2>=DATEVALUE("4/1/2015")),100000,IF(AND(C2="2014-Q2",R2>=DATEVALUE("7/1/2015")),100000,IF(AND(C2="2014-Q3",R2>=DATEVALUE("10/1/2015")),100000,IF(AND(C2="2014-Q4",R2<DATEVALUE("1/1/2016")),100000,0))))))

 

[Caribeiro77]

Try to put the complete formula you have, since the one above is incomplete again.
You have to use spaces before and after the operators >=

 

[ariel20029]

=IF(AS5="DUP",0,IF(BB5>=1,0,IF(AND(C5="2014-Q1",R5>=DATEVALUE("4/1/2015")),100000,IF(AND(C5="2014-Q2",R5>=DATEVALUE("7/1/2015")),100000,IF(AND(C5="2014-Q3",R5>=DATEVALUE("10/1/2015")),100000,IF(AND(C5="2014-Q4",R5<DATEVALUE("1/1/2016")),100000,0))))))

Ok sorry this is the complete formula.
Sharon

 

[ariel20029]

It keeps cutting off the formula.
,R5>=DATEVALUE("10/1/2015")),100000,IF(AND(C5="2014-Q4",R5<DATEVALUE("1/1/2016")),100000,0))))))

 

[ariel20029]

a

 

[ariel20029]

I don't know what to do it keeps cutting off the formula.

=IF(AS5="DUP",0,IF(BB5>=1,0,IF(AND(C5="2014-Q1",R5>=DATEVALUE("4/1/2015")),100000,IF(AND(C5="2014-Q2",R5>=DATEVALUE("7/1/2015")),100000,IF(AND(C5="2014-Q3",R5>=DATEVALUE("10/1/2015")),100000,IF(AND(C5="2014-Q4",R5<DATEVALUE("1/1/2016")),100000,0))))))