Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over complicated Calculation?
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over complicated Calculation?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over complicated Calculation?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over complicated Calculation?
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 On Sep 2, 10:21*am, quetzalc0atl wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Over complicated Calculation?
Thats worked a treat Kostis thank you very much. I can't believe it
was that straight forward. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated working days calculation | Excel Worksheet Functions | |||
Help with complicated calculation | Excel Worksheet Functions | |||
Help with complicated calculation | Excel Worksheet Functions | |||
complicated calculation in my pivot table | Excel Worksheet Functions | |||
Complicated | Excel Worksheet Functions |