change value by date range

[Guest]

I would like to have one cell that will decrease in value by 1.5 every 1st
day of the month

 

[Domenic]

Try...

=5000/(1.5^SUMPRODUCT(--(DAY(ROW(INDIRECT(DATE(2005,3,1)&":"&TODAY())))=1
)))*1.5

....where 5000 is the starting value used as an example, or...

=A1/(1.5^SUMPRODUCT(--(DAY(ROW(INDIRECT(DATE(2005,3,1)&":"&TODAY())))=1))
)*1.5

....where A1 contains your starting value.

Note that I've used the first day of the current month as a starting
date [DATE(2005,3,1)]. If you're going to use another month as your
starting date, make sure that you use the first day of that month.

Hope this helps!

 

[Jason Morin]

This'll last ya until the end of 2007:

=A1-MATCH(TODAY(),DATE(2005,ROW(INDIRECT("4:36")),1),1)
*1.5

Array-entered, meaning press ctrl + shift + enter after
copying in the formula.

A few other notes:

*Assumes the value to decrement is in A1
*First time to decrement 1.5 is April 1, 2005.
*The formula will show #N/A until April 1, 2005.

HTH
Jason
Atlanta, GA

 

[N Harkawat]

=100-(1.5*INT((TODAY()-DATEVALUE("1/1/2005"))/30))
assuming that the starting value you want reduced by 1.5 is 100 and the
starting date is 1/1/05