We help IT Professionals succeed at work.

# One small bug in this formula

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

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27422864.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*
Comment
Watch Question

## View Solution Only

CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
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
27422864v3.xls

Commented:
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!