Hi

It's unclear, how exactly you calculate your job counts for every area, but
generally you must generate some general table, and then use it as source
for a pivot Pivot.

P.e. you have table JobCnt with columns LSOA, JobCnt where you enter your
data. Expand this table adding calculated columns Area, Weight,
SomeCalculatedValue.
Area and Weight vlaues are read from another table
p.e. LSOAInfo: LSOA, Area,Weight
(NB! the LSOA as key column must be leftmost one), using VLOOKUP. And then
some value based on JobCnt, and Weight is calculated for every LSOA.

Now based on table JobCnt, you create a pivot table, where calculated values
from table JobCnt are summed grouped by Area. (The sum of calculated values
over ares must return the value you want to get)


Arvi Laanemets



"quetzalc0atl" wrote in message
...
Dear Learned Excel Gurus,

I'm currently working on a calculation which I believe to be quite
complicated. I was wondering if anyone could please help me simplify
or solve this problem.

I'm currently trying to re-calculate job counts from one smaller
geographic area (LSOAs) to another larger one (Neighbourhoods). Many
of the LSOAs crosscut the larger neighbourhood areas and have been
given weightings of where they fall. Some neighbourhoods may contain
one or two LSOAs and some can contain as many as 10 LSOAs. There are
43 neighbourhoods and 160 LSOAs.

E.g.

Neighbourhood LSOA Weighting
Area1 LSOA1 40
Area1 LSOA2 20
Area1 LSOA3 100
Area1 LSOA4 100
Area2 LSOA2 20
Area2 LSOA5 95
Area2 LSOA6 75
Area2 LSOA7 60
Area2 LSOA8 99
Area2 LSOA9 80
Area3 LSOA2 60
Area3 LSOA1 20
Area3 LSOA10 35
etc

Given the table below I need to calculate the job count for each of
the 43 Neighbourhood areas using the weightings lookup table above
which is on another tab.

LSOA Job Count
LSOA1 25
LSOA2 20
LSOA3 5
LSOA4 35
LSOA5 15
LSOA6 30
LSOA7 15
LSOA8 35
LSOA9 25
LSOA10 45
etc

This calculation is something I wish to distribute to other
departments - most of which do not use MS Access. I've been racking
my brains for a good few days now on how to do this in Excel but I
have drawn a complete blank. I think I need some fresh perspective on
this - any help would be greatly appreciated.

Thanks,

Clive