Calculation based on 2 seperate ranges

[Excel_VBA_Newb]

Okay, I will try to explain this as best I can. I have, essentially, a
bulleted list (WBS in Program Management term). I want to do a sum of all
rows based on the bulleted hierarchy.

For Example:

(1st Range: contains the headings: A, AA, AAA, B, BA, BAA, etc)
(2nd Range: contains the values to base the percentages off of)
Total
A - Percentage of Total
AA - Percentage of A
AAA - Percentage of AA
AAB - Percentage of AA
AAC - Percentage AA
AACA - Percentage of AAC
AB - Percentage of A
B - Percentage of Total
BA - Percentage of B
BAA - Percentage of BA
BAB - Percentage of BA
BABA - Percentage of BAB
etc. etc. etc

The hierarchy is never constant. The Bulleted item (A, B, etc) is held in
one Range, and the values are held in another. What would be the best way to
iterate through the range and determine how to perform the percentage
calculations? I would imaging doing a LEN on the bulleted item, but not quit
sure how to iterate through both ranges and keeps the second range in synch
during the for loop on the first.

Any help is appreciated!

 

[Patrick Molloy]

can you give some numbers for these please?
it difficult to see what the difference is for example between BAB and BAC
apart from saying their percentages of BA. Whats the distinction?

 

[Excel_VBA_Newb]

Here is an example: (Hope it helps)

TOTAL $16,466,712
A $1,195,572 7%
AA $357,581 30%
AB $825,000 69%
AC $17,580 1%
ACA $12,133 69%
ACB $5,448 31%
ACBA $4,725 87%
ACBB $723 13%

 

[Patrick Molloy]

for A , where does 7% come from as the value is close to 13%
the rest I get OK

sorry for late reply - went rollerblading as the rain stopped lol

 

[Patrick Molloy]

forget my earlier question re A <> 7% it is, I'm a nonce doh

if your data is in columns A (A,AA,AB etc) and the values are in column B

In C2 put this formula and replicate down:

=IF(LEN(A2)=1,B2/$B$1,B2/(SUMIF($A$2:$A$9,LEFT(A2,LEN(A2)-1),$B$2:$B$9)))

 

[Excel_VBA_Newb]

Thanks for your assistance.

"A" is 7% of the total: ($16,466,712 x .072 = $1,195,572)

 

[Excel_VBA_Newb]

Thanks for your help with the algorithm, Patrick.

However, I need to implent this into a range using a for loop (to test if a
value exists). Because the range will always be unique (different values, and
different levels), I would think I would need to load the range, test for
character length, and then perform the calculation. The problem I'm having is
how to hold the value when the character length is longer. For example:

Does character exist: Yes - continue No - Exit
Is character length 1: Yes - Divide by static cell (B$2$) No - Divide by
previous cell that is 1 character in length (How do I hold this value if the
character length runs 2 for several cells.

See the example below. When I get to ACB, how do I hold the value to divide
by? And then dynamically change that divisor again?

A -
AA - Divide by A
AB - Divide by A
AC - Divide by A
ACA - Divide by AC
ACB - Divide by AC
ACBA - Divide by ACB
AD - Divide by A

 

[Patrick Molloy]

See the example below. When I get to ACB, how do I hold the value to

divide
by? And then dynamically change that divisor again?

the formula takes care of this as is - see my work file:
http://cid-b8e56c9a5f311cb7.skydriv...xcel Files/General Stuff/Excel|_VBA|_Newb.xls

you'll obviously generate errors if the underlying data doesn't exist

See the example below. When I get to ACB, how do I hold the value to
divide
by? And then dynamically change that divisor again?

the formula takes ABC and looks up the value for AB, it doesn't "hold"
anything
for ABL where L is any letter, the code will look for AB
This is the SUMIF part of the formula:
LEFT(A2,LEN(A2)-1), being the criteria

 

[Excel_VBA_Newb]

Gotcha. Thank you for your help...this one was stumping me.

 

[Excel_VBA_Newb]

Thanks, Patrick.

the formula takes care of this as is - see my work file:
http://cid-b8e56c9a5f311cb7.skydriv...xcel Files/General Stuff/Excel|_VBA|_Newb.xls

you'll obviously generate errors if the underlying data doesn't exist


the formula takes ABC and looks up the value for AB, it doesn't "hold"
anything
for ABL where L is any letter, the code will look for AB
This is the SUMIF part of the formula:
LEFT(A2,LEN(A2)-1), being the criteria