Question
Thread display: Collapse - |
All Answers
Start or search
Create a new discussion
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
How can you create and maintain a column of historical peak scores in Excel
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?