asked on

One small bug in this formula

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

https://www.experts-exchange.com/questions/27422864/Conditional-formula-to-format-Personal-Best.html

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*

barry houdini

Hello Tony,

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

=COUNT($A2:A2)*(B2<MIN($A2:A2))*(B2<>"")

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

regards, barry
27422864v2.xls

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Tony Davidson

ASKER

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!