Conditional Formatting - based on a formula and named cells

Closed
Report
Posts
2
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 26, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

I have been struggling to get a simple formula to work in Excel 2013. My objective is to compare budget numbers to actual and if we are within an acceptable range, have the cell format appropriately.

I have created two named ranges so I can easily adjust the thresholds. These thresholds are desired percentages ..

LowThreshold 0.85
HighThreshold 1.1

F4 - Month1 Budgeted Number
F5 - Month1 Actual Number

What I was hoping was to have a formula as simple as ...

=(f4/f5)< $lowthreshold (colour red)
=(F4/F5)>$highthreshold (colour green)

Excel is accepting the formula, but there isn't a format change occurring. I haven't started on the between statement yet.

Any Assistance would be appreciated.

3 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
see the sheet for which url is given.
under contiditional formatting click manage rules

http://speedy.sh/fKp4k/burustu-130620.xlsx
1
Posts
2
Registration date
Tuesday June 18, 2013
Status
Member
Last seen
June 26, 2013

that works well - thank you! I guess we are not able to use named ranges, but that is obviously solved through the absolute cell.

Question: do you know how to conditionally format a cell if it falls between the low and high values ?

example

=F4/F5<$B$2 (colour red)
=F4/F5 is between $B$1 and $b$2 (Colour yellow) << -
=F4/F5>$B$2$(colour green)
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
you can open conditional formatting window type the formula with a = sig


=F4/f5<B2 like that.

=and(f4/f5>b1,F4/F5<B2)

if b1 is lower value and b2 hilgher value
0