• 412K Members
• 4,099 Online
• 468K Conversations

New Contributor

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

8 Replies

# Re: Conditional Formatting Help Please!

Hi David,  I used two conditional formats, one for below 250 and one for above, have attached a file rather than trying to explain.

# Re: Conditional Formatting Help Please!

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

# Re: Conditional Formatting Help Please!

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

# Re: Conditional Formatting Help Please!

That could be the rule formula for the green as

`=A1>=(MAX(\$A1:\$K1)+MIN(\$A1:\$K1))/2`

and similar for another color

# Re: Conditional Formatting Help Please!

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!

# Re: Conditional Formatting Help Please!

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)`

# Re: Conditional Formatting Help Please!

Thank you so much Sergei, perfect!!

# Re: Conditional Formatting Help Please!

You are welcome

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies