03-06-2019 08:24 AM
03-06-2019 08:24 AM
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
03-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.
03-07-2019 05:11 AM
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
03-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 :)
03-07-2019 06:08 AM
That could be the rule formula for the green as
and similar for another color
03-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!
03-07-2019 08:25 AM
Oh, it was hard to make such guess.
For the green the formula could be
since below C it's also green, and for yellow