I need a formula that finds the most recent value in a list, where some dates in that list didn't have readings. For instance, in the table below, I need a formula that will calculate the Counts/Day in cell C2 (
bolded). The dates between today's reading and the previous reading will be random...the last reading may have been from the previous day, or the last reading may have been a week ago. I need the formula to be able to search for the most recent reading (02/12/22), pull the Cycle counter reading for that most recent reading (19,300), subtract it from the current day's reading (20,500), and then divide by the elapsed days (2) to ultimately give me the Counts/Day (600). I can't simply use a
=large formula since the Cycle Counter may roll over (in which case I need it to show "ERROR" or be blank....doesn't matter, I'm fine with an error).
| DATE | Cycle Counter Reading | Counts/Day | |
| 02/14/22 | 20,500 | 600 | |
| 02/13/22 | | | |
| 02/12/22 | 19,300 | | |
| 02/11/22 | 19,700 | | |
I appreciate any help that you Excel wizards can give me
.
Thanks!
Chris
Ok. Let's assume columns A, B, and C for your values above, you can modify as needed, but this is what I came up with. You show office 365 so I know that has MAXIFS (not 100% on 2016).
The basic formula in column C would be as follows:
=IF(A2<>MaxDate,"",IF(MaxDateValue<PreviousDateValue,"Error",(MaxDateValue-PreviousDateValue)/(MaxDate-PreviousDate)))
A2 would be your first date, and then you could copy down. I used named ranges in my sample data, but you don't have to, obviously (use Name Manager if you do, it looks "cleaner" in my opinion, but I digress). I created a table of 3 columns by 20 rows (row 1 is header, so data was 2-20, but feel free to expand on that as well).
MaxDate=MAXIFS($A$2:$A$20,$B$2:$B$20,">"&0)
MaxDateValue=VLOOKUP(MaxDate,$A$2:$B$20,2,FALSE)
PreviousDate=MAXIFS($A$2:$A$20,$B$2:$B$20,">"&0,A$2:$A$20,"<"&MAXIFS($A$2:$A$20,$B$2:$B$20,">"&0))
PreviousDateValue=VLOOKUP(PreviousDate,$A$2:$B$20,2,FALSE)
The formula works like this. First, while all column C has the formula, only the row that matches the MaxDate will populate.
It then gives an error (or however else you would like), if the value for the most recent date (MaxDateValue) is less than the value for the date before it that has a value (PreviousDateValue). If not, then it takes the MaxDateValue and subtracts the PreviousDateValue before dividing by the difference between MaxDate and PreviousDate.
The MaxDate formula just looks at the data and takes the value of the most "newest" date that has a cycle counter reading.
The PreviousDate formula is similar to MaxDate, only this time it excludes the MaxDate, so it will take the "second newest" date (2/14/22 in your example).
MaxDateValue and PreviousDateValue are just simple vlookups based upon the MaxDate and PreviousDate.
Hope this helps.
