Software

Question

Gravatar
0 Votes
Locked

How can you create and maintain a column of historical peak scores in Excel

By martin.peirson ·
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.

Thread display: Collapse - | Expand +

All Answers

gravatar
Collapse -

Store maximum values as static data in Excel

by Al Kostiuk In reply to How can you create and ma ...

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.

Back to Software Forum

Start or search

Related Discussions

Related Forums