formula of which the result stays 'frozen' (not recalculated)

Occasional Visitor

My problem is as follows:

I made an Excel sheet with on every line the date and more (running) specs. (speed, distance etc etc.) Some of these values are based on my weight which is written at the top of my sheet.

When my weight alters ALL calculations in the spreadsheet (so also the lines before the current date) are re-calculated. How can I keep these old values (based on the weight of that moment) the same? Of course I can make an extra cell at every line with my weight at that dat, but my weight does not change on a daily base

So, I am looking for a formula, not a way of copy and paste (values), and preferably not a macro.

Thanks in advance,

Jan Hage (Netherlands)

1 Reply


You may enter your weight not every day but once per month or once per year, whatever, and use XLOOKUP or VLOOKUP with approximate mode to find weight for the current date as one which was defined for closest previous to it date.