If Then Question from Newbie. . .

H

hospitalgreg

I have the following basic spreadsheet set up:

A B C D E F
G
1 Service Jan Feb Mar Total
Avg
2 Orthopedics 52 28 60 140 47

I would like the monthly figures (C2, C3, C4) to turn red if they are 20%
below the running Avg (G2). I am entering the monthy numbers directly. Any
suggestions would be greatly appreciated.

Thanks in advance.
 
B

Bernard Liengme

Use Format | Conditional Formatting
1) select C2,C3,C4
2) Use menu command Format | Conditional Formatting
3) In dialog set Formula Is as =C2<$G$2*20% and set font colour to red
Use Format Painter to format cells in other rows
best wishes
 
E

Elkar

Use Conditional Formatting.

Select your range of data
From the Format Menu, select "Conditional Formatting..."
Set Condition to "Less than" and enter =$F2*0.8
Set your Format (red)
Click OK

Note that this formula will need to be manually changed if you add columns
for future months later, such that F is no longer the Average.

HTH,
Elkar
 
E

Elkar

Oops, I meant to put =$G2*0.8

Elkar said:
Use Conditional Formatting.

Select your range of data
From the Format Menu, select "Conditional Formatting..."
Set Condition to "Less than" and enter =$F2*0.8
Set your Format (red)
Click OK

Note that this formula will need to be manually changed if you add columns
for future months later, such that F is no longer the Average.

HTH,
Elkar
Click to expand...
 
G

Gary L Brown

Assumption:
Headings -
Col A = Service
Col B = Jan
Col C = Feb
Col D = Mar
Col E = Apr
Col F = May
Col G = Jun
Col H = Jul
Col I = Aug
Col J = Sep
Col K = Oct
Col L = Nov
Col M = Dec
Col N = Total
Col O = Average

You need to create 12 separate Conditional Formats, one for each column from
B (Jan) to M (Dec).
1) Highlight Col B.
2) Select Format>Conditional Formatting...
3) In the 1st box of 'Condition 1', select 'Formula Is'
4) In the 2nd box of 'Condition 1', enter...
=$B1<0.8*AVERAGE($B1:$M1)
5) In 'Condition 1', select 'Format...'
6) On the 'Font' tab, select 'Color:'
7) Select the color RED
8) Select OK to get out of the Font window
9) Select OK to get out of 'Conditional Formatting.
10) Highlight Col C and repeat the process.

Formulas for each Conditiona Formatting column:
Col B =$B1<0.8*AVERAGE($B1:$M1)
Col C =$C1<0.8*AVERAGE($B1:$M1)
Col D =$D1<0.8*AVERAGE($B1:$M1)
Col E =$E1<0.8*AVERAGE($B1:$M1)
Col F =$F1<0.8*AVERAGE($B1:$M1)
Col G =$G1<0.8*AVERAGE($B1:$M1)
Col H =$H1<0.8*AVERAGE($B1:$M1)
Col I =$I1<0.8*AVERAGE($B1:$M1)
Col J =$J1<0.8*AVERAGE($B1:$M1)
Col K =$K1<0.8*AVERAGE($B1:$M1)
Col L =$L1<0.8*AVERAGE($B1:$M1)
Col M =$M1<0.8*AVERAGE($B1:$M1)

HTH,
 
G

Gary L Brown

If you really only want Jan/Feb/Mar then change the part of the formula...
<0.8*AVERAGE($B1:$M1)
to read
<0.8*AVERAGE($B1:$D1)
HTH,
 
Top