Complex SUM *** Variant of previous post

[vsoler]

I just posted some hours ago a similar problem and I got satisfactory
answers.
That's why I am encouraged to ask a new question that is closer to my
actual problem.

Here it goes.

In a range I have a list of expenses per department:

Allocation table 1 2
Dpt Expense1 Expense 2
a 1 3
b 2 4
c 3 5
a 4 1
c 5 7

Say that Allocation tables are in B1:C1.
Say that expenses are in A3:C7 (excluding headers)

As you can see, one department can have multiple expenses
An allocation table is a table that describes how the expenses of the
departments are allocated
to the end users, which are in turn the departments themselves.

In another range, I have the tables accordint to which expenses are
allocated:

1 a b
a 0.3 0.7
b 0.1 0.9
c 1 0

Say it is in A21:C23, excluding headers


2 a b
a 0.6 0.4
b 0.25 0.75
c 0.5 0.5

Say it is in A31:C33, excluding headers


Now, suppose that I want the total expense after allocation for each
Dept in each expense (A9='a', A10='b')?
The formulas I need should be in B9:C10

For B9 (dept='a', expense1) the result should be:
1*0.3+2*0.1+3*1+4*0.3+5*1 = 9.7
For C10 (dept='a', expense2) the result should be:
3*0.6+4*0.25+5*0.5+1*0.6+7*0.5 = 9.4

Of course, my model is a lot bigger and more complex. I expect to
input a formula in B9 and copy it down and right to C10. I would not
like to use auxiliary cells for intermedite results.

Any help is highly appreciated.

 

[JP Ronse]

Hi Vicente,

Are you sure about your calculations?

You take dpt a = dpt c. Is this correct and so yes, why do you make
difference?

Trying to understand what you want to do.

Wkr,

JP

 

[vsoler]

Hi Vicente,

Are you sure about your calculations?

You take dpt a = dpt c. Is this correct and so yes, why do you make
difference?

Trying to understand what you want to do.

Wkr,

JP

Hello JP Ronse,

Unless I made a typing mistake my post is correct. I don't understand
why you say that 'dpt a = dpt c'.

In my production model (real life model) Expense1 is, say Actual 2008,
and Expense2 is say Budget 2009. It happens that the allocation of
expenses varies each year according to several parameters: sales
volume made by each product line, ressources allocated to each
department, etc.

In my real life problem some departments are only support departments
that give service to the terminal departments (those that collect
espenses from other departments apart from their own).

Because I wanted to make it simple, in my post I used 3 departments
('a', 'b' and 'c') out of which only two are considered terminal ('a'
and 'b'). As per the coefficients I typed, I just used some random
figures, because what matters is the calculation.

Perhaps when I tried to make my example simple, getting rid of
anythind that was not strictly necessary for the post, I happen to
make it more compex. I apologise for this.

Should you need any further details, do not hesitate to ask.

Thank you again.

 

[JP Ronse]

Hi Vicente,


I was a bit too fast with previous reply, did not analyse your calculation
in depth.

I think you need a customer function for this.

What is still not clear...

In expenses 1and 2, you have a third column (b) with other weight, what is
it and when to use?

Wkr,

JP

Hi Vicente,

Are you sure about your calculations?

You take dpt a = dpt c. Is this correct and so yes, why do you make
difference?

Trying to understand what you want to do.

Wkr,

JP

Hello JP Ronse,

Unless I made a typing mistake my post is correct. I don't understand
why you say that 'dpt a = dpt c'.

In my production model (real life model) Expense1 is, say Actual 2008,
and Expense2 is say Budget 2009. It happens that the allocation of
expenses varies each year according to several parameters: sales
volume made by each product line, ressources allocated to each
department, etc.

In my real life problem some departments are only support departments
that give service to the terminal departments (those that collect
espenses from other departments apart from their own).

Because I wanted to make it simple, in my post I used 3 departments
('a', 'b' and 'c') out of which only two are considered terminal ('a'
and 'b'). As per the coefficients I typed, I just used some random
figures, because what matters is the calculation.

Perhaps when I tried to make my example simple, getting rid of
anythind that was not strictly necessary for the post, I happen to
make it more compex. I apologise for this.

Should you need any further details, do not hesitate to ask.

Thank you again.

 

[T. Valko]

Try these:

B10:

=SUMPRODUCT(B3:B7,LOOKUP(A3:A7,A21:B23))

C10:

=SUMPRODUCT(C3:C7,LOOKUP(A3:A7,A32:B34))

Note that your allocation tables *must* be sorted in ascending order as is
shown in your posted sample.

 

[vsoler]

Try these:

B10:

=SUMPRODUCT(B3:B7,LOOKUP(A3:A7,A21:B23))

C10:

=SUMPRODUCT(C3:C7,LOOKUP(A3:A7,A32:B34))

Note that your allocation tables *must* be sorted in ascending order as is
shown in your posted sample.

T. Valko,

Your suggested solution does not seem to work in my system. It looks
as though there is something wrong with the LOOKUP function.

Regards

 

[vsoler]

Hi Vicente,

I was a bit too fast with previous reply, did not analyse your calculation
in depth.

I think you need a customer function for this.

What is still not clear...

In expenses 1and 2, you have a third column (b) with other weight, what is
it and when to use?

Wkr,

JP







Hello JP Ronse,

Unless I made a typing mistake my post is correct. I don't understand
why you say that 'dpt a = dpt c'.

In my production model (real life model) Expense1 is, say Actual 2008,
and Expense2 is say Budget 2009. It happens that the allocation of
expenses varies each year according to several parameters: sales
volume made by each product line, ressources allocated to each
department, etc.

In my real life problem some departments are only support departments
that give service to the terminal departments (those that collect
espenses from other departments apart from their own).

Because I wanted to make it simple, in my post I used 3 departments
('a', 'b' and 'c') out of which only two are considered terminal ('a'
and 'b'). As per the coefficients I typed, I just used some random
figures, because what matters is the calculation.

Perhaps when I tried to make my example simple, getting rid of
anythind that was not strictly necessary for the post, I happen to
make it more compex. I apologise for this.

Should you need any further details, do not hesitate to ask.

Thank you again.

JP Ronse,

Expense1 column allocates costs according to table 1, which is:

1 a b
a 0.3 0.7
b 0.1 0.9
c 1 0

This means that the cost of dept 'a' is allocated, in 30% to terminal
dept 'a' and in 70% to terminal dept 'b' which makes a total of 100%.

I hope now everything is a bit clearer.

Regards

 

[T. Valko]

Your suggested solution does not seem to

work in my system. It looks as though there
is something wrong with the LOOKUP function.

Here's a small sample file that demonstrates this.

xvsoler.xls 14kb

http://cjoint.com/?jgvXp1YKk8

--
Biff
Microsoft Excel MVP

Try these:

B10:

=SUMPRODUCT(B3:B7,LOOKUP(A3:A7,A21:B23))

C10:

=SUMPRODUCT(C3:C7,LOOKUP(A3:A7,A32:B34))

Note that your allocation tables *must* be sorted in ascending order as is
shown in your posted sample.

T. Valko,

Your suggested solution does not seem to work in my system. It looks
as though there is something wrong with the LOOKUP function.

Regards

 

[Shane Devenshire]

Actually, I seem to agree with an earlier response. You entered "a" in A9
and "b" in A10 then you say C10 should be dept a. So what result would you
expect in B10? and in C9. I would think that a in A9 means something
relative to the lookup tables in A21:C23 and A31:C33, it would seem that a
should correspond to the column labeled "a" in those tables and b to the "b"
in those tables? But if not, we need to know what you expect in C9 and B10
or you need to revisit your sample calculation:
"For C10 (dept='a', expense2) the result should be:
3*0.6+4*0.25+5*0.5+1*0.6+7*0.5 = 9.4 "