Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
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. " wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
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())*vl ookup($A15;$A$9:$B$11;2;0)" Hope its help Marcelo from Sao Paulo-Brazil " escreveu: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
soory there is a mistake on the row numbers
"Marcelo" escreveu: A B C D E 3 name Jan Feb Mar Apr 4 John 0 200 50 622 5 Brian 50 75 200 415 6 Peter 150 0 50 354 7 8 9 BU1 0,2 10 BU2 0,5 11 BU3 0,3 12 13 14 John Jan Feb Mar Apr 15 BU1 0 40 10 124,4 16 BU2 0 100 25 311 17 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())*vl ookup($A15;$A$9:$B$11;2;0)" Hope its help Marcelo from Sao Paulo-Brazil " escreveu: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
can an array formula do this?
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
referencing the value of a cell containing an array formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |