can an array formula do this?

[barbetta3141]

Hello gurus,

Here's my setup. In the row of a revenue table I have:

(commas are new columns)
Name, Jan, Feb, Mar, Apr, etc
John, 0, 200, 50, 622, etc

Elsewhere, I have:

Business Unit, John
BU1, 20%
BU2, 50%
BU3, 30%

I want to create a table that allocates the data in the first table
based on the percentages in the second:
John, Jan, Feb, Mar, etc
BU1, 0, 40, 10, etc
BU2, 0, 100, 25, etc
BU3, 0, 60, 15, etc

Any suggestions on an array formula that can do this? Thanks.

 

[Guest]

I wouldn't use an array formula here, but named ranges. If you name the
range where you have the BU1 value with "BU_1", BU2 as "BU_2" and BU3 as
"BU_3" you can use the INDIRECT formula directly with the row header. For
example:
=VLOOKUP($A$6,$A$1:$E$3,COLUMN(),FALSE)*INDIRECT($A7)
Supposing that the first table with the data is in A1:E3, the table to fill
starts in A6, with the "John" name, and "BU_1" as the row header in A7. The
use of the COLUMN() here may not fit your specific layout.

Hope this helps,
Miguel.

 

[Guest]

A B C D E
1 name Jan Feb Mar Apr
2 John 0 200 50 622
3 Brian 50 75 200 415
4 Peter 150 0 50 354
5
6
7 BU1 0,2
8 BU2 0,5
9 BU3 0,3
10
11
12 John Jan Feb Mar Apr
13 BU1 0 40 10 124,4
14 BU2 0 100 25 311
15 BU3 0 60 15 186,6



The formula that use on B13 as follow:

"=Index($A$3:$E$6;match($A$14;$A$3:$A$6);COL())*vlookup($A15;$A$9:$B$11;2;0)"

Hope its help
Marcelo from Sao Paulo-Brazil


"(e-mail address removed)" escreveu:

 

[Guest]

soory there is a mistake on the row numbers

"Marcelo" escreveu:

 

[barbetta3141]

What about if I want a table with the totals for all employees broken
down by BU and Month, without having to make a table for each employee?

Name, Jan, Feb, Mar, Apr, etc
John, 0, 200, 50, 622, etc
Jane, 100, 40, 20, 52, etc
Joe, 122, 493, 20, 94, etc

Name, BU1, BU2, BU3, BU4
John, 20%, 50%, 30%, 0%
Jane, 0%, 50%, 50%, 0%
Joe 10%, 0%, 0%, 90%

What I'm looking for:

All employees, Jan, Feb, Mar, etc
BU1, sumproducts
BU2, sumproducts
BU3, sumproducts

That's the array formula I'm looking for. Sorry, should've said that
in the original post.

 

[barbetta3141]

What about if I want a table with the totals for all employees broken
down by BU and Month, without having to make a table for each employee?

Name, Jan, Feb, Mar, Apr, etc
John, 0, 200, 50, 622, etc
Jane, 100, 40, 20, 52, etc
Joe, 122, 493, 20, 94, etc

Name, BU1, BU2, BU3, BU4
John, 20%, 50%, 30%, 0%
Jane, 0%, 50%, 50%, 0%
Joe 10%, 0%, 0%, 90%

What I'm looking for:

All employees, Jan, Feb, Mar, etc
BU1, sumproducts
BU2, sumproducts
BU3, sumproducts

That's the array formula I'm looking for. Sorry, should've said that
in the original post.

 

[barbetta3141]

Sorry I don't know why this response double-posted an hour later. I'm
not trying to be aggressive or bump this message up in the queue or
anything. Actually, I posted this under another subject (crazy triple
array) because I didn't describe the problem correctly the first time.
But thanks for all the suggestions... I'm getting closer.