Conditional Formatting - based on a formula and named cells

Closed
burusu Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 26, 2013 - Jun 18, 2013 at 08:43 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 27, 2013 at 12:42 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 20, 2013 at 07:54 AM
see the sheet for which url is given.
under contiditional formatting click manage rules

http://speedy.sh/fKp4k/burustu-130620.xlsx
1
burusu Posts 2 Registration date Tuesday June 18, 2013 Status Member Last seen June 26, 2013
Jun 26, 2013 at 12:15 PM
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
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 27, 2013 at 12:42 AM
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