returning certain amounts for certain date ranges

[RussianRedneck]

Hi there,

I am trying to automate populating a cell with a certain tuition amount for a fiscal year. Basically, I have a cell with a date. If that date falls between 9/1/16 and 8/31/17, I need the formula to return a value of $4,991. If it is between 9/1/17 and 8/31/18, the value needs to be $5,490, and so on. If it is any easier, this amount increases 10% every fiscal year. I've been working on this for over two hours, and can't seem to figure it out.

Thank you so much for your input!

[Phuocam]

Try this ...

=4991*(DATEDIF(DATE(2016,9,1),A1,"Y")*0.1+1)

[Arkadi]

The only difference I had was adding a round function since you didn't indicate a .1 on the tuition for the next year

Please Login or Register  to view this content.

[Phuocam]

A little shorter ...

=4991*((YEAR(A1+122)-2017)*0.1+1)

[RussianRedneck]

Thanks, this works, and it is different from how I was approaching it. However, the 10% increase needs to be not from the base number, but an incremental increase rather, based on the last year's tuition (4991, 5490 (4991*1.1), 6039 (5490*1.1), 6643 (6039*1.1), and so on.

[Phuocam]

Maybe like this ...

=4991*1.1^(YEAR(A1+122)-2017)