Excel formula based on previous month

Question

I inherited a worksheet that has a number of formulas (see below). The formula is trying to pull the rate from the previous month but once the month changes the amount is 0, so the current month = 0. Surely there must be a better way to write this formula but I'm stuck. Any suggestions would be greatly appreciated. Thanks for your help.

Jan  500
Feb  =IF(MONTH(TODAY())=2,x7,"0")
Mar  =IF(MONTH(TODAY())=3,Y7,"0")
Apr  =IF(MONTH(TODAY())=4,Z7,"0")
May  =IF(MONTH(TODAY())=5,AA7,"0")
Jun  =IF(MONTH(TODAY())=6,AB7,"0")

It's not clear what you're asking - are you looking for a formula for a single cell that looks up the previous month's total from a list? — Kryten
@kryten....I'm checking with the user to see if the rates change month to month. if they don't, then I think they need to refer to the January column in their formula instead of the previous month. The way they have it set up, once the month changes, the previous month is 0, so no amount carries forward... — Shaves
If the month is March, what do you want February and April to be? Should April still be 0? Should February still be 500 (January number)? — mooseman

mooseman mooseman · Accepted Answer · 2020-06-02T15:45:19

I think what you need is to just check to see if today's date is less than the month you are evaluating for. This may be what you are looking for:

Feb =IF(MONTH(TODAY()) < 2,0,x7)
March =IF(MONTH(TODAY()) < 3,0,y7)

Excel formula based on previous month

1 Answers