replacing missing values with an average: soduku?

[knightta2]

Hi guys, if you can help me with this it will same me many hours, I'm pretty sure a formula or macro can do this, but I don't have the expertise to write one. Any help would be great.

Problem:

I have a list of Standard Industrial Codes (SIC) for businesses and employment against these codes.

2 digit SIC is eg 20
3 digit SIC represents more detailed subdivisions eg
201
202
203
etc

At '2 digit SIC' all the data are available, but at 'SIC 3 digit' some data are starred out as would identify businesses. Ie disclosure control has been applied to the data.

What I want to do is replace the starred out data with a best guess.

For example the table below shows SIC 2 digit between 17 and 22 and employment on the rhs column.

Taking the 2 digit code 20 we see total employment is 1630, but a number of 3 digit SIC codes below are stared out - 203,205 and 206. But we know together they make 1630 - (310+0+154)=1166

So I'd like to replace the starred out values in 203,205 and 206 with an average value of 1166/3 =389

I'd like to do this for all 3 digit SICs as in the example below - in reality I have a much longer list - is there a macro or formula which could do this. It would have to take the difference between the 2 digit SIC total and the total of the 2 digit SICs and apportion this to the starred out 3 digit SIC codes.

17 1530
18 1740
19 44
20 1630
21 1571
22 6135
171 *
172 *
181 1740
182 0
191 0
192 44
201 310
202 0
203 *
204 154
205 *
206 *
211 *
212 *
221 1341
222 4794

 

[AlphaFrog]

Excel Workbook
	A	B	C
1	17	1530
2	18	1740
3	19	44
4	20	1630
5	21	1571
6	22	6135	Estimated
7	171	*	765
8	172	*	765
9	181	1740
10	182	0
11	191	0
12	192	44
13	201	310
14	202	0
15	203	*	389
16	204	154
17	205	*	389
18	206	*	389
19	211	*	786
20	212	*	786
21	221	1341
22	222	4794
Sheet

 

[knightta2]

Just to say thanks very much this works a treat and is a great method for assigning best guess data to missing values by apportioning down.

Something I'll use a lot (I'm a Statistician who deals with business data).

All the best, Tom.