Remember Me?

#1
July 31st 08, 07:59 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: May 2007 Posts: 79
Complex cost allocation formula

Hello everyone,

I am faced to an accounting problem that has emerged at the time of
the 2009 budgeting process.

I prepare the yearly budget in an Excel table that has 3 columns: the
first two are codes, the last one is a figure.

Account CostCenter Amount
1 a 12
1 b 13
1 c 8
2 a 21
2 a 3
2 c 7
3 a 12
3 b 4

Then, my budget goes through an allocation or assignment process to
end users. In the example, the total cost of CostCenter a is allocated
in 30% to User X and in 70% to user Z.

CostCenter User PCT
a X 30%
a Z 70%
b Y 55%
b Z 45%
c X 15%
c Y 85%

Eventually, I want to get a table like the one below, where the 4.8 is
calculated as 12*30%+8*15%

Account User Amount2
1 X 4,8
1 Y 13,95
1 Z 14,25
2 X 8,25
2 Y 5,95
2 Z 16,8
3 X 3,6
3 Y 2,2
3 Z 10,2

I can get the results I want by using an intermediate auxiliary table.
No problem here.
However, I am interested in obtaining directly my Amount2 column
without intermediate calculations. I am ready to accept array formulas
if needed.

Can anybody help?

Thank you very much.

#2
July 31st 08, 02:46 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jun 2008 Posts: 1,358
Complex cost allocation formula

The way I see it, you will need an intermediary table. This table, however,
can be automated, and hidden. This solution is also assuming that in your
'final' table, you are keying in the account and the User, and just need the
Amount 2 to populate. I can post it if you like. But won't if my assumptions
are incorrect.
--
John C

"vsoler" wrote:

Hello everyone,

I am faced to an accounting problem that has emerged at the time of
the 2009 budgeting process.

I prepare the yearly budget in an Excel table that has 3 columns: the
first two are codes, the last one is a figure.

Account CostCenter Amount
1 a 12
1 b 13
1 c 8
2 a 21
2 a 3
2 c 7
3 a 12
3 b 4

Then, my budget goes through an allocation or assignment process to
end users. In the example, the total cost of CostCenter a is allocated
in 30% to User X and in 70% to user Z.

CostCenter User PCT
a X 30%
a Z 70%
b Y 55%
b Z 45%
c X 15%
c Y 85%

Eventually, I want to get a table like the one below, where the 4.8 is
calculated as 12*30%+8*15%

Account User Amount2
1 X 4,8
1 Y 13,95
1 Z 14,25
2 X 8,25
2 Y 5,95
2 Z 16,8
3 X 3,6
3 Y 2,2
3 Z 10,2

I can get the results I want by using an intermediate auxiliary table.
No problem here.
However, I am interested in obtaining directly my Amount2 column
without intermediate calculations. I am ready to accept array formulas
if needed.

Can anybody help?

Thank you very much.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post [email protected] Excel Worksheet Functions 1 January 26th 08 12:00 PM ryguy7272 Excel Worksheet Functions 7 January 21st 08 07:47 PM Chunkey Pandey New Users to Excel 1 November 25th 06 09:40 PM CotoJoe Excel Discussion (Misc queries) 0 September 14th 05 08:23 PM Space Ape Excel Worksheet Functions 0 July 3rd 05 07:32 AM

All times are GMT +1. The time now is 04:04 AM.