Over complicated Calculation?

[quetzalc0atl]

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

 

[Arvi Laanemets]

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]

Apologies if it is unclear.

Assuming that the Neighbourhood, LSOA, Weightings table is on Sheet1
starting in Cell A1 and that the LSOA, Job Count table is on Sheet2
starting in Cell A1.

The basic formula is Weighting of LSOA in Neighbourhood / 100 * LSOA
job Count + Weighting of LSOA in Neighbourhood / 100 * LSOA job Count
etc

To calculate the job count for Area 1, 2 and 3 I would use the
following formulas

Area1 =Sheet1!C2/100*Sheet2!B2+Sheet1!C3/100*Sheet2!B3+Sheet1!
C4/100*Sheet2!B4+Sheet1!C5/100*B5
Area2 =Sheet1!C6/100*Sheet2!B3+Sheet1!C7/100*Sheet2!B6+Sheet1!
C8/100*Sheet2!B7+Sheet1!C9/100*Sheet2!B8+Sheet1!C10/100*Sheet2!
B9+Sheet1!C11/100*Sheet2!B10
Area3 =Sheet1!C12/100*Sheet2!B3+Sheet1!C13/100*Sheet2!B2+Sheet1!
C14/100*Sheet2!B11

I thought this could be automated by concatenating columns A and B on
Sheet1 and performing a lookup on them. However I expereinced
difficulties when trying to configure what value to lookup. I thought
I would have to have a list of every possible combination of
Neighbourhood and LSOA on Sheet2 then check to see which ones existed
on Sheet1 then draw another lookup to bring that value into the
calculation. Which seems to over complicated to me.

So from what you have suggested Arvi this can be done by use of a
pivot table?

Thanks,

Clive

 

[vezerid]

Clive,

You need to bring Job Count info next to your weights table with
VLOOKUP. So in D2 of Sheet1 (weights) use:

=VLOOKUP(B2,Sheet2!A:B,2,0)

Once you have the expanded table you can list all your area codes in a
separate area, say Sheet3!A2:A21. Then, next to Area1, in Sheet3!B2
use:

=SUMPRODUCT((Sheet1!$A$2:$A$101=A2)*Sheet1!$C$2:$C$101*Sheet1!$D$2:$D
$101)/100

Copy down

HTH
Kostis Vezerides

 

[quetzalc0atl]

Thats worked a treat Kostis thank you very much. I can't believe it
was that straight forward.