Mar 06 2019 08:24 AM
Hi,
I am trying to set up conditional formatting for when a number is above or below the mid part of a range.
E.g. the lower range is 200 and the upper range is 300, if the number I have is 249 or below I want the cell to highlight green and if it is 250 or above (up to 300) then I want it to highlight yellow.
I am sure it is very simple and I am just over complicating it massively! Any help would be greatly appreciated
Thanks
Mar 07 2019 04:16 AM
Hi David, I used two conditional formats, one for below 250 and one for above, have attached a file rather than trying to explain.
Mar 07 2019 05:11 AM
Hi Rich
Thanks for this but maybe I should have been a bit clearer. The upper and lower ranges differ on each row, it might be 200-300 but the next row will be 825-1000, the next row 525-675 etc. I then have a value in another cell and I need to format that cell green if it is closer to the upper range and yellow if it is closer to the lower range
Hope that makes sense
Mar 07 2019 05:55 AM
the only way i know of as to how to do this would be to use an extra column & add a formula then use that to do your conditional formatting
ie if column A is you list of numbers have column b as if(a<(half your number),1,0) then have 2 condition formats to work from the 1 or the 0
hope this helps :)
saf
Mar 07 2019 06:08 AM
That could be the rule formula for the green as
=A1>=(MAX($A1:$K1)+MIN($A1:$K1))/2
and similar for another color
Mar 07 2019 07:25 AM
Thanks Sergei but it doesn't seem to be quite what I need (unless I am doing something wrong). If you see the below, column A is what I need formatting - red if it is higher than column D, green if it is equal to or less than column C - this is sorted. What I am struggling with is if column A is closer to column C than to column D, but not exactly between, I want it to colour green but if it is closer to, or exactly between, column D than column C, then I want it to colour yellow. So below A2 should colour green as it is closer to column C and A6 should colour yellow as it is exactly between column C & D. Hopefully some visuals make it easier to understand!
Mar 07 2019 08:25 AM
Oh, it was hard to make such guess.
For the green the formula could be
=$A2<($C2+$D2)/2
since below C it's also green, and for yellow
=($A2>=($C2+$D2)/2)*($A2<=$D2)