Formula to decrease value of cell

  • Thread starter Thread starter Flyboy
  • Start date Start date
F

Flyboy

Need to be able to have cell B4 decrease by 1 every 30 days until it reaches 0

I would also like to be able to take values in A17 move up one if A16 is
empty.
 
One way...............

enter in B4 ....=30-(TODAY()-40116)/30
40116 is the number for today the 30th of october
tomorrow the formula will give you
30 -(40117-40116)/30
or
30 - 1/30
the day after it will be
30 - 2/30

you can format the cell to have a few decimals to see where you are.
or you can add a "round()" function if you need whole numbers
The second part of your question would have to be done with a macro and you
would need to specify when or how often. I.e. check once per day?

Greetings from New Zealand
 
Without using code, you'll have to assign *other* cells to contain your
"starting points", for values and dates.

Say A1 for starting value, and A2 for starting date.

Then try something like this in B4 for question #1:

=MAX(A1-IF(TODAY()>=A2,LOOKUP(TODAY()-A2,{0,30,60,90,120,150,180,210,240;0,1,2,3,4,5,6,7,8}),0),0)

And something like this in A17 for #2:

=1*(A16="")+A1
 
Back
Top