Formatting Values in a table dependant on number of corresponding values

[superdaw]

I have a table with data set against dates, ie test results against test dates.
Some days 3 tests are performed, some days two.

eg
date result
day1 a 10
day1 b 11
day2 a 10
day2 b 15
day2 c 14
day3 a 11
day3 b 10

I want to format column B to highlight results above a certain value.
however, the limits for highlighting are different depending on the number of tests in a day.
eg day with 2 tests limit = 15
day with 3 tests limit = 11
conditionally formatting the column wont work as it wont differentiate between tests per day.
Is there a way of getting around this?
Theres a lot of data and trying to separate the twos and threes would be a nightmare!

[Special-K]

Where are your tests limit in the above data? Just create another column with the test limits for each day.
Why wont Conditional Formatting work? If you have rules just apply those rules to the condition, e.g.
if day2 means 2 test limit

=(AND(RIGHT(A1,1)=2,C1 > 15))
will highlight all day2s where the result is greater than 15

=(AND(RIGHT(A1,1)=3,C1 > 11))
will highlight all day3s where the result is greater than 11

What does a b c signify in the above?

[bmouse]

I kinda think that wont work because the actual data probably will have dates in it not "day 1", "day 2" and "day 3"...

[Punnam]

superdaw
Explain in details what to be happen & where to be happen
Take case 1 of Day 1
Day 1 two tests are conducted
the value for one - 10 & the other 11
The limit which you have specified is 15 . so what should be happened & where should it be happened.
Punnam

[superdaw]

a, b and c signify different tests.
for example 1/1/13 3 tests performed, all tests are equivalent, results vary slightly.
2/1/12 2 tests performed, again tests are equivalent to each other, but slightly different to tests performed on 1/1/13 (2 per day vs. 3 per day)

I have a list of dates with a letter signifying test in column A, and test results in column b.

I can change column A to a list of dates without a letter differentiating between them, which sort of groups them how I want,
however I don't know how to conditionally format column B (results) based on:

(i) the value in column B
(ii) the number of equivalent dates in column A

If there are 3 x 1/1/13 I want to format these results corresponding to that date based on a limit value x,
if there are 2 x 2/1/13 I want to format these results corresponding to that date based on a limit value y.

If I could split column B into two (i.e. 3 test days and 2 test days) that would be fine, but how would I do that?

[bmouse]

Try these 2 rules in CF, seemed to work for me. I also applied different colors for each rule. Change the cell numbers and values as needed.

=AND(COUNTIF($H$34:$H$42;H34)>=2;I34>15)
=AND(COUNTIF($H$34:$H$42;H34)>=3;I34>11)

[superdaw]

Here's an attachment to make things a bit clearer!

[Punnam]

Hi,

Check this file .

Punnam

[bmouse]

I believe this one works as well.

[Punnam]

Hi,

Check the updated File as requested .
With A help of helper column

Punnam

[Punnam]

@ bmouse

Yes post 9 also works fine until a ">="&"<=" symbol is is added in condition .
Rest is fine .

@ superdaw,
It is up to you which one are you preferring both of them are same but with an extra Column

Punnam

[bmouse]

Hi,

Check the updated File as requested .
With A help of helper column

Punnam

E15, E23, E24 should be highlighted, E14 shouldn't be highlighted I think.

[bmouse]

Final variant.

=AND(COUNTIF($D$12:$D$24;D12)>=2;E12>8;E12<12)
=AND(COUNTIF($D$12:$D$24;D12)>=2;E12>15;E12<17)

[Punnam]

@ bmouse ,

It is OP choice which are to be highlighted & not be .

Punnam

[bmouse]

@ bmouse ,

It is OP choice which are to be highlighted & not be .

Punnam

Well, if the rule states that on 2 tests performed 15 to 17 should be highlighted, then 16 fits the criteria and should be highlighted, doesn't it?

[Punnam]

Under stood updated the same .
Lit bit confusion
=IF(AND(COUNTIF($D$12:$D$24,D12)=2,D12>=$N$9,E12<=$P$9),"PASS",IF(AND(COUNTIF($D$12:$D$24,D12)=3,D12>=$N$8,E12<=$P$8),"PASS",""))
Thanks for correct me .

Punnam

[superdaw]

Cheers guys! you were both a lot of help!