We help IT Professionals succeed at work.

One small bug in this formula

Tony Davidson
I've just noticed an issue with the new formula as per this question (that has been answered)


If there is a gap (such as event not run during a week), then after that the first time a score is beaten it will be a PB.  Eg.

19, 18*, 18.5,17.9*,blank,19,18.9*,blank, 25, 24.1*, 17.8*

The * show what will be highlighted, but in fact only the following should be highlighted:

19, 18*, 18.5,17.9*,blank,19,18.9, blank, 25, 24.1, 17.8*
Watch Question

Most Valuable Expert 2013

Hello Tony,

Did you use the revised formula I suggested with COUNT, i.e.


See the attached updated example - I put those values in row 2 and only the correct ones are formatted I think?

regards, barry
Most Valuable Expert 2013
Sorry, I missed out one value - should be like new attachment

Note that in my example column A has names so isn't included - do you have it setup like that or with data starting at column A - that could affect the outcome?

regards, barry


You're right. And I realise what I did wrong in my testing.

I need a second formula for field events - where increases are a PB. I changed the < to a > and assumed (incorrectly) that this was correct.

Of course if I had spent more than 0.3 seconds on it I would have realised that changing the "Min" to "Max" as well as < to > in your formula would ensure it works correctly.

So all good. Thanks for your prompt response!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.