Conditional Formatting - based on a formula and named cells Closed

Question

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.

venkat1926 · Answer

see the sheet for which url is given.
under contiditional formatting click manage rules

http://speedy.sh/fKp4k/burustu-130620.xlsx

burusu · Answer

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)

venkat1926 · Answer

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

Conditional Formatting - based on a formula and named cells

3 responses

Similar discussions

Subscribe To Our Newsletter!