We help IT Professionals succeed at work.

One small bug in this formula

Tony Davidson
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

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

Author

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!

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