Sum in hierarchy
 

Hi, I'm stuck on how to calculate sum within hierarchy. Below is what
I'd like to archive. How can I create a macro that in the end will
WRITE A FORMULA into the cells for every level? For example, at Level
0, the formula is sum all Level1, at level1 = sum all level2 and so on.
The levels are not limited to 4 but will change dynamically.

I'd tried to looking for a start and end row for each level and use it
in a sum formula at the upper level. But I got confuse myself.
Nothing's working. Any ideas are very appreciated.

1. L0 = SUM (ALL L1)
2. --L1 = SUM(ALL L2)
3. ---L2 = SUM (ALL L31)
4. ----L31 = SUM (ALL L41)
5. -----L41 10
6. -----L41 26
7. -----L41 33
8. ----L31 = SUM (ALL L42)
9. -----L42 77
10.-----L42 25
11.-----L42 15
12.---L2 = SUM (ALL L32)
13.----L32 = SUM (ALL L43)
14.-----L43 11
15.-----L43 55
16.----L32 = SUM (ALL L44)
17.-----L44 4
18.-----L44 78

Please help,
hideki


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=515368

Sum in hierarchy
 

Hideki,

Have you tried Subtotals (Data Subtotals...) and Grouping? It sound
like that will accomplish what you are trying to do.

Hope This Helps,

Conan Kelly



"hideki" wrote
in message ...

Hi, I'm stuck on how to calculate sum within hierarchy. Below is
what
I'd like to archive. How can I create a macro that in the end will
WRITE A FORMULA into the cells for every level? For example, at
Level
0, the formula is sum all Level1, at level1 = sum all level2 and so
on.
The levels are not limited to 4 but will change dynamically.

I'd tried to looking for a start and end row for each level and use
it
in a sum formula at the upper level. But I got confuse myself.
Nothing's working. Any ideas are very appreciated.

1. L0 = SUM (ALL L1)
2. --L1 = SUM(ALL L2)
3. ---L2 = SUM (ALL L31)
4. ----L31 = SUM (ALL L41)
5. -----L41 10
6. -----L41 26
7. -----L41 33
8. ----L31 = SUM (ALL L42)
9. -----L42 77
10.-----L42 25
11.-----L42 15
12.---L2 = SUM (ALL L32)
13.----L32 = SUM (ALL L43)
14.-----L43 11
15.-----L43 55
16.----L32 = SUM (ALL L44)
17.-----L44 4
18.-----L44 78

Please help,
hideki


--
hideki
------------------------------------------------------------------------
hideki's Profile:
http://www.excelforum.com/member.php...o&userid=18903
View this thread:
http://www.excelforum.com/showthread...hreadid=515368

Sum in hierarchy
 

Hideki,

This is not an answer unless you can get your base data recut but I'll
suggest it anyway. If your hierarchy information were laid out in columns to
the left of your data, then you could use grouping & outlining.

Regards,

Chris.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"hideki" wrote:


Hi, I'm stuck on how to calculate sum within hierarchy. Below is what
I'd like to archive. How can I create a macro that in the end will
WRITE A FORMULA into the cells for every level? For example, at Level
0, the formula is sum all Level1, at level1 = sum all level2 and so on.
The levels are not limited to 4 but will change dynamically.

I'd tried to looking for a start and end row for each level and use it
in a sum formula at the upper level. But I got confuse myself.
Nothing's working. Any ideas are very appreciated.

1. L0 = SUM (ALL L1)
2. --L1 = SUM(ALL L2)
3. ---L2 = SUM (ALL L31)
4. ----L31 = SUM (ALL L41)
5. -----L41 10
6. -----L41 26
7. -----L41 33
8. ----L31 = SUM (ALL L42)
9. -----L42 77
10.-----L42 25
11.-----L42 15
12.---L2 = SUM (ALL L32)
13.----L32 = SUM (ALL L43)
14.-----L43 11
15.-----L43 55
16.----L32 = SUM (ALL L44)
17.-----L44 4
18.-----L44 78

Please help,
hideki


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=515368

Sum in hierarchy
 

I'd came close with below loops. But it gave me #VALUE or #NAME error.
Here is what I did, I go to the Lowlevel (eg 4) and record it row's
number - the column is still unchange. It's loops until found a level
that less one than current level. Then the record will restart.

Problem here is, I record column and row's number as string. It's seems
to cause the error. It became like A119, A118, A117 etc. In the final
I'd like to make it became "=SUM(A119,A118,A117)" but the actual
formula wrote in the cell was =SUM('A119','A118','A117') and it's not
summing anything. Any idea how can I make the formula working?

Here is my code:

strSum = ""
For lngLevel = LevelMax To 1 Step -1
For lngRow = lngLastRow To 3 Step -1
Select Case .Cells(lngRow, colLevel).Value
Case lngLevel
strSum = strSum & strColTotal & lngRow & ","
Case lngLevel - 1
strSum = Mid(strSum, 1, Len(strSum) - 1) & ")"
..Cells(lngRow, colTotal).FormulaR1C1 = "=SUM(" &
strSum & ")"
strSum = ""
End Select
Debug.Print strSum
Next
Next

Any ideas are really appreciated.


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=515368