Nested if function for a range of dates & formulas....or another solution

[memphis123]

Hello all and thank you in advance for any advice.

Microsoft 2016

I have dates listed in and based on how far away the date is from today in decimal forum I need to add 2 cells together. As an example, if the item x is 6 months from now, I need to add a cell to the price shown therefore increasing the price.

This is my current formula that does not work. I was under the assumption a nested if function was the best solution, but it may not be.

=(IF(AND(D2>H6,D2< i6),b2+j6),if(and(d2>H5,D2< i5),b2+j5),if(and(d2>H4,D2< i4),b2+j4),if(and(d2>H3,D2< i3),b2+j3),if(and(d2>H2,D2< i2),b2+j2,"check"))<i6),b2+j6),if(and(d2><i5),b2+j5),if(and(d2><i4),b2+j4),if(and(d2><i3),b2+j3),if(and(d2><i2),b2+j2,"check"))

D= time in years (decimal form) from today
H= upper bound of time range
I = lower bound of time range
B = price
j = amount I need to increase price by if d falls withing the range between h & I.</i2),b2+j2,"check"))
</i3),b2+j3),if(and(d2></i4),b2+j4),if(and(d2></i5),b2+j5),if(and(d2></i6),b2+j6),if(and(d2>

 

[jtakw]

Hi,

This is Only correcting your IF formula, you had closed off your IF checks before "value if false" at each test except the last one:

=IF(AND(D2>H6,D2< I6),B2+J6,IF(AND(D2>H5,D2< I5),B2+J5,IF(AND(D2>H4,D2< I4),B2+J4,IF(AND(D2>H3,D2< I3),B2+J3,IF(AND(D2>H2,D2< I2),B2+J2,"check")))))

 

[memphis123]

jtakw, when I tried this excel, gives me an autocorrection to only one two parentheses in the formula.

 

[memphis123]

Nevermind, I think this worked. Thank you.

 

[jtakw]

You're welcome, welcome to the forum.