Max value from conditional formatting

Question

Would it be possible to select the max value from a conditional formatting rule?

I require for cells T5: T14 to contain the max values of column G according to the color ranges which have been created with

'format only cells that contain'

'cell value'

'between'

'Colored values in column R'

'Colored values in column S'

Answer 1

Sorry - I left of the final ) 

=MAX(IF($G$3:$G$1000<=S5,$G$3:$G$1000))

Make sure that you have entered it using Ctrl-Shift-Enter - If you do it correctly, 

Excel will all a leading { and a final } around the formula. This shows that the max value less than the value in S5 - 1900 - is correctly returned.

Answer 2

Thank you for your time and patients.

Answer 3

Thank you for your time Bernie

The formula above equates the maximum of all the numbers and not just the maximum of each color category.

although the formula does limit the range from g5:g1000 to <=2000 it gives the max of all values in column g

Answer 4

In T5, use the array formula (entered using Ctrl-Shift-Enter)

=MAX(IF($G$3:$G$1000<=S5,$G$3:$G$1000)

Though you may need to use ; instead of , 

=MAX(IF($G$3:$G$1000<=S5;$G$3:$G$1000)

Change the 1000 to a larger number if you have more rows.

Then copy down to T6:T14