zaterdag 14 september 2013

Excel: marking moving maximum using conditonal formatting

I have a temperature table  with all the average month temperatures of De Bildt (Netherlands) since the year 1706. So, is it possible to indicate what the highest average month temperature for july was until a specific year and so on?

We can use Conditional Formatting to create a rule to mark this moving maximum. In the example I have, I am doing this for the columns B til M starting in cell B2 by using the formula:

=B2>=SUBTOTAL(4,B$2,B2)

In the Dutch version this would be:

=B2>=SUBTOTAAL(4;B$2;B2)

In the function SUBTOTAL number 4 indicates we want the maximum value.



When we link a background color to this rule, we could get this:


For each column we see the month with the highest average temperature until that year marked with the color orange or so.

You can also download the file voorbeeld.zip through this link:
Een reactie plaatsen