change value by date range
I would like to have one cell that will decrease in value by 1.5 every 1st
day of the month |
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! In article , "Mikel" wrote: I would like to have one cell that will decrease in value by 1.5 every 1st day of the month |
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 -----Original Message----- I would like to have one cell that will decrease in value by 1.5 every 1st day of the month . |
=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 "Mikel" wrote in message ... I would like to have one cell that will decrease in value by 1.5 every 1st day of the month |
All times are GMT +1. The time now is 08:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com