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?
This conversation is currently closed to new comments.
You will need to store the max score value as actual data and not the result of a formula since the source data changes every day. You will need an equation to check the current value against the max value and a macro to overwrite if greater. There is a "Paste Special - Value" function that will paste the value of a formula to a cell.
If Cell (C2) holds the IF statement to test if the value in C1 is greater than C3. Formula in C2, =IF(C1 > C3, C1, C3) Macro contents Sub Copy_Max_Value() Range("C2").Copy Range("C3").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=False End Sub
You can setup the macro to run on open or close and it will copy the result in C2 over to C3 every time.
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?