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.

What you want is called Conditional Formatting. Take a look here for
some good stuff on Conditional Formatting.

http://www.cpearson.com/excel/cformatting.htm


hospitalgreg wrote:
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.

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"hospitalgreg" wrote in message
...
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.

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



"hospitalgreg" wrote:

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.

Oops, I meant to put =$G2*0.8

"Elkar" wrote:

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



"hospitalgreg" wrote:

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.

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 FormatConditional 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,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"hospitalgreg" wrote:

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.

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,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Gary L Brown" wrote:

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 FormatConditional 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,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"hospitalgreg" wrote:

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.