| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculation based on 2 seperate ranges
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calculation based on 2 seperate ranges
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy and Paste seperate ranges in excel | Excel Programming | |||
| union of named ranges based only on the names of those ranges | Excel Programming | |||
| calculation a average dependated of values in seperate column | Excel Worksheet Functions | |||
| copy all named ranges in a sheet to seperate sheets | Excel Programming | |||
| How do I return a calculation based on several ranges? | Excel Worksheet Functions | |||
Linear Mode
