Conditional formatting (or some other method)...

[wardnine]

I have this table:

sampletable.xls
	A	B	C	D	E	F	G	H	I	J	K	L
4	STATE		REVENUES				EXPENDITURES
5
6			TOTAL	FEDERAL	STATE	LOCAL	TOTAL	CURRENT	CAPITAL	OTHER	DEBT	ASSETS
7								SPENDING	OUTLAY
8
9	United States................................................................		6,912,961,561	577,851,986	3,384,155,708	2,950,953,867	7,120,866,001	6,120,917,568	792,997,957	206,950,476	3,953,662,909	1,993,337,830
10
11	Alabama........................................................................		81,541,985	8,912,953	46,581,602	26,047,430	83,152,318	74,782,790	6,786,780	1,582,748	35,611,352	21,933,387
12	Alaska...........................................................................		22,387,384	4,072,941	12,769,925	5,544,518	25,281,537	20,932,684	3,877,979	470,874	10,764,564	-
13	Arizona...........................................................................		101,822,083	11,627,090	45,728,275	44,466,718	97,501,506	79,741,242	13,574,879	4,185,385	63,671,146	25,183,287
14	Arkansas........................................................................		50,684,451	5,965,168	37,591,075	7,128,208	50,721,472	45,719,625	3,881,747	1,120,101	27,844,452	17,986,140
15	California........................................................................		910,115,231	90,991,784	527,798,926	291,324,521	901,331,175	772,186,053	115,262,194	13,882,929	383,017,122	418,888,262
16
17	Colorado.........................................................................		98,311,940	6,283,438	42,628,938	49,399,563	104,315,589	87,213,280	12,891,725	4,210,583	85,991,161	44,575,911
18	Connecticut....................................................................		107,348,515	5,510,716	38,965,846	62,871,953	103,711,625	93,028,780	8,475,959	2,206,886	34,875,054	495,775
19	Delaware........................................................................		18,960,890	1,444,180	12,473,210	5,043,499	20,067,269	17,192,379	2,688,578	186,312	5,125,595	1,562,590
20	District of Columbia..........................................................		17,490,758	2,395,804	-	15,094,953	17,363,619	14,313,455	3,050,165	-	-	-
21	Florida............................................................................		306,875,685	30,721,807	136,419,514	139,734,365	316,828,229	263,541,817	45,908,904	7,377,509	159,182,536	92,642,042
1

This table actually includes data for all 50 states but it is too large to be posted on the board in its entirety. I'm basically looking to create a series of add-check edits within the table, but not exactly sure of the easiest way to set this up. There are three edits I want to set up:

1) the sum of federal, state, and local revenues in columns d, e, and f should be within 1 of the total revenues in column c. If not within 1, I wanted the total in column c to be shaded red

2) the sum of current spending, capital outlay, and other expenditures in columns h, i, and j should be within 1 of the total expenditures in column g.
If not within 1, I wanted the total in column g to be shaded red.

3) the sum of data for all the states from rows 11-70 (all rows are obviously not shown in the partial table I have above due to size restrictions on this board) should be within 1 of the u.s. total in row 9. Each column here needs to be checked individually. If not, I wanted the u.s. total in row 9 to be shaded red.

Is it possible to set this up without going through and typing in conditional formatting code for each individual cell? There are about 15 other tables in addition to this one where I need to do something very similar (though not exactly the same) so was looking to see if I could somehow copy and paste the conditional fomatting formulas to other cells without changing the actual values or border formats of the other cells? Is there an easier way to do this without using conditional formatting for cells?

Any advice is appreciated, thanks!!!

Julie

 

[Brian from Maui]

Julie,

What do you mean by within 1? 1%, literally 1....

 

[wardnine]

literally 1...

 

[wwbwb]

for #1, highlight c11:c70 and apply a conditional formating of

=$c11<$d11+$e11+$f11-1
=$c11>$d11+$e11+$f11+1

for #2, hightlight g11:g70 and apply a conditional formating of

=$g11<$h11+$i11+$j11-1
=$g11>$h11+$i11+$j11+1

for #3, highlight c9 and apply a conditional formatting of

=c9<sum(c11:c70)-1
=c9>sum(c11:c70)+1