Dave

[caino41]

In 2003, I want to apply conditional formatting to a cell that is summing monthly data (12 cells with data added to each subsequent cell by month). I want that cell to average any data entered in the monthly cells, then provide a background color based on parameters (let's say the monthly target is 10): between 70% and 130%, green; between 50% and 70%, yellow; between 130% and 150%, yellow; and below 50% or above 150%, red. Any thoughts or input would be helpful.

 

[texasalynn]

in Excel 2003 you can only have up to 3 conditions. Will have to have VBA for more

 

[Norie]

Actually you can have more than 3 conditions - the limit is 3 formats.

Mind you the conditions you want to use aren't particularly clear - to me anyway.

 

[yytsunamiyy]

Lynn, there only are 2 conditions?

Monthly Data in Colums A to N, Target in Column M, Conditional format applies to M

- Standard format red fill

1st Condition - yellow fill:
=OR(AND(M2=>0.5*AVERAGE(A2:N2),M2<=0.7*AVERAGE(A2:N2)),AND(M2=>1.3*AVERAGE(A2:N2),M2<=1.5*AVERAGE(A2:N2)))

2nd condition - green fill:
=AND(M2>0.7*AVERAGE(A2:N2),M2<1.3*AVERAGE(A2:N2))

 

[caino41]

Not sure what you mean by standard red fill and if that is a "Formula Is" or a "Cell Value Is." I put your yellow and green in under "Formula Is." Is that correct?

 

[yytsunamiyy]

There was a small mistake in my Formulas, which I have adjusted now.

According to your question, your cells should always be red if neither the condition for yellow nor the condition for green are fulfilled. Therefore you can just format the background of all cells in that column to be red. That's what I mean with standard fill.

Have a look at the following example to understand the workings of the formulas. For ease of reading I have put the formulas used for conditional formating of Column M in columns N-P. Copy the example in a blank workbook and play with the numbers. Evaluating the formula in O2 & P2 will also aid understanding.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Target
2	1	2	3	4	5	6	7	8	9	10	11	12	4		FALSCH	WAHR
3															6,5	6,75
4														<0.5
5														=>0.5<=0.7		3,375
6														>0.7	4,55
7														<1.3	8,45
8														=>1.3<=1.5		10,125
9														>1.5
Tabelle1

Cell Formulas
Range	Formula
O2	=AND(M2>0.7*AVERAGE(A2:L2),M2<1.3*AVERAGE(A2:L2))
O3	=AVERAGE(A2:L2)
O6	=O3*0.7
O7	=O3*1.3
P2	=OR(AND(M2<=0.7*AVERAGE(A2:L2),M2>=0.5*AVERAGE(A2:L2)),AND(M2<=1.5*AVERAGE(A2:L2),M2>=1.3*AVERAGE(A2:L2)))
P3	=AVERAGE(B2:M2)
P5	=P3*0.5
P8	=P3*1.5

Best regards,

Stephan

 

[caino41]

Stephan,

Thank you very much for all the work you put into your response. The numbers look good and my problem is solved!

Thanks again,
Dave

 

[yytsunamiyy]

always happy to help and even happier to get a thank you

 

[Scott Huish]

BTW, Conditional Formatting would be a better subject for this thread than Dave

 

[yytsunamiyy]

But hardly worth hassling an admin over hotpepper, which would be the only way to change the thread title now. After all, there are a felt 1million threads with the title "Conditional Formating" on this here board. Had Dave used google he might even have found the solution without posting another one of those. Mind you, try to come up with a good, descriptive thread title or indeed search string if you never heard of conditional formatting