Conditional arguement formula with two conditions

[stevembe]

Firstly thanks for taking the time to read. I need assistance with a conditional formatting issue in that in Column AD I have a two decimal point number which I need to turn shaded yellow if the number is greater than 8 but less than 12 and if column AI has the text Non Member. Also to turn shaded red if the number is 12 or greater and if column AI has the text Non Member.

Many thanks for any assistance.

 

[JackDanIce]

For first request try:

=AND(A1="Non Member",MEDIAN(AD1,8,12)=AD1)

and format cells to be Yellow when this is true

For second request try:

=AND(A1="Non Member",AD1>=12)

and format cells to be Red when this is true

 

[stevembe]

For first request try:

=AND(A1="Non Member",MEDIAN(AD1,8,12)=AD1)

and format cells to be Yellow when this is true

For second request try:

=AND(A1="Non Member",AD1>=12)

and format cells to be Red when this is true

Many thanks for the response but I tried both and whilst no error is returned the cells do not change fill colour and remain the same. Just to clarify the text Non Member is in Column AI2 and the number is in AD2 so I used:

[=AND(AI2="Non Member",MEDIAN(AD2,8,12)=AD2)
/CODE]

 

[JackDanIce]

Did making the corrections work?

 

[stevembe]

Did making the corrections work?

I am afraid not, the first formula highlights some 12 or more but not them all. The second doesn't change anything.

 

[JackDanIce]

I tried to mock up your sheet and it works for me. Have you checked the range it applies to matches the range your data is in?

 

[stevembe]

I tried to mock up your sheet and it works for me. Have you checked the range it applies to matches the range your data is in?

It's strange it picks up some but not others e.g. 28.05, 20.40 and 19.80 are some of the values that remain un-formatted.

Thanks for your time by the way.

 

[JackDanIce]

You're welcome. It may be different cells have different formatting or number types which is making it behave unexpectedly. Try checking the number format and data type is consistent.