Conditional Formatting based on a criteria

I have a row of values and a Standard Deviation calculation at the end of each row. I have a cell on the same sheet with an SD number in.

I wish to colour the cell in the row which has the highest and lowest number in IF the SD of that row is below the value of the cell with the SD number in.

I have tried varies formulas with conditional formatting and have not managed this yet.

Any help gratefully received. Using MAC Office Excel Ver 16.4. Huge thanks.

Best wishes,


Stephen

I have setup data in A2:N15, sd in O2:O15, the SD threshold in a named cell SD. Adjust these formulae to your actual data

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AND(A2=MIN($A2:$N2),$O2<SD)
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=AND(A2=MAX($A2:$N2),$O2<SD)[/FONT][SUB][SUP]
[/SUP][/SUB][/FONT]

Dear Bob,

Brilliant - huge thanks and it works - thank you so much.

One last question if I may - I have just noticed that a number of my rows have 0 in and I want to avoid the zero being picked up for the MIN - what can I weave into the formula =AND(A2=MIN($A2:$N2),$O2<SD) to overcome this please ?

Thank you once again for the great help - much appreciated.

Best wishes,


Stephen

This should sort it for you

=AND(A2=MIN(IF($A2:$N2=0,99^99,$A2:$N2)),$O2<SD)

Many thanks Bob - that works a treat.

Huge thanks for your help and stay safe during these troubled times !!