Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help: Can I change the date formula from mm/dd/yyyy to "mmm-yy" et | Excel Discussion (Misc queries) | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) | |||
HOW TO USE A FORMULA TO CHANGE CELL COLOR ACCORDING TO DATE RANGE. | Excel Worksheet Functions | |||
How to change (delivery) days and automatically the receive date in an other cell? | Excel Worksheet Functions |