I have a spreadsheet in the form of a matrix. Each row represents a client and each column represents a different scoring category. Column AY calculates the total score for each client. The scoring categories are updated daily and result in regular changes to each client’s totals, both upwards and downwards.
What I’m trying to do is to create a new column in BA which maintains the highest total to date for each client.
For instance, if the total in AY3 was 450 yesterday but increases to a new peak of 480 today, the 480 is shown in BA3. However, if AY3 falls to 420 tomorrow, the peak score 480 will still shown in BA3 and will only change if AY3 goes above 480.
I’ve tried using a simple formula in BA3, ‘=IF(AY3>BA3,AY3,BA3)’ to demonstrate what I’m trying to achieve, but this is a circular reference which doesn’t work, in that it doesn’t retain the peak score, even though the Options > Calculations > Iteration box is checked.
I figure this ought to be achievable, maybe if the peak score was stored dynamically within the formula itself or perhaps creating an auto open macro that automatically copies the formula calculating the totals and pastes the values as a reference to apply the peak formula to… but then my brain begins to hurt! I’ve pretty much admitted defeat on this one, so please, can anyone help?