Conditional formatting based on maximum deviation in range

I have a range of cells containing measurements, formatted as fractions to 2 digits: 68 3/16, 67 7/8, 67 3/4, 67 15/16, etc. I am trying to add conditional formatting that will analyze and highlight the range based on 3 scenarios

Green: difference from largest to smallest numbers is between (and including) 0 & 1/4
Yellow: difference from largest to smallest numbers is between 1/4 and 1/2
Red: difference from largest to smallest is greater than 1/2

I have multiple sets of ranges, independent of one another by a blank row right now. Can I add some kind of IF statement for the CF based on if there is a number in the cell above or below to set the range, or how can I apply this to the entire column?

Thanks in advance!

Will the Elevation column always be made up of a single character followed by 1 or more digits?

Also, please confirm that you are still using XL2013 - later versions have the MAXIFS and MINIFS functions, which would make things a bit easier.

Pete

elevation column will not always be single character, but probably 95% of the time. If my scope has more than 26 areas being measured, the naming convention may change slightly

working in excel for microsoft 365 - can confirm I have access to MAXIFS and MINIFS

To show you how this could be done, put this formula in cell F4 of your sample file, and copy down to F62:

=IF(C4="","",MAXIFS(D:D,C:C,LEFT(C4)&"*")-MINIFS(D:D,C:C,LEFT(C4)&"*"))

It will give you the difference between the largest and smallest within each Elevation group, or return a blank on blank rows. You could use this helper column to determine the colour to display within Conditional Formatting - let me know if you need help in setting up the rules.

Hope this helps.

Pete

Thanks Pete - I think that will get me what I want to do. Didn't think about doing it this way, but should be able to set up the rules based on the helper column.