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!

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" <(E-Mail Removed)> wrote in message
news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
> 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!

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
> > 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!
>

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

"Excel_VBA_Newb" <(E-Mail Removed)> wrote in message
news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in
>> message
>> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
>> > 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!
>>

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" <(E-Mail Removed)> wrote in message
news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in
>> message
>> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
>> > 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!
>>

Thanks for your assistance.

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

"Patrick Molloy" wrote:

> 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
>
> "Excel_VBA_Newb" <(E-Mail Removed)> wrote in message
> news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
> >> > 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!
> >>

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
> >> > 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!
> >>

> 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.skydrive...BA%7C_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" <(E-Mail Removed)> wrote in message
news:EE3CF602-B1AA-4363-BEED-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in
>> message
>> news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
>> > 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" wrote:
>> >
>> >> 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" <(E-Mail Removed)> wrote in
>> >> message
>> >> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
>> >> > 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!
>> >>

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



"Patrick Molloy" wrote:

> > 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.skydrive...BA%7C_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" <(E-Mail Removed)> wrote in message
> news:EE3CF602-B1AA-4363-BEED-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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" <(E-Mail Removed)> wrote in
> >> >> message
> >> >> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
> >> >> > 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!
> >> >>

Thanks, Patrick.

"Patrick Molloy" wrote:

> > 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.skydrive...BA%7C_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" <(E-Mail Removed)> wrote in message
> news:EE3CF602-B1AA-4363-BEED-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in
> >> message
> >> news:3B6268C9-291B-473D-8EE6-(E-Mail Removed)...
> >> > 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" wrote:
> >> >
> >> >> 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" <(E-Mail Removed)> wrote in
> >> >> message
> >> >> news:504C0EFB-6F39-4487-8FF8-(E-Mail Removed)...
> >> >> > 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!
> >> >>