I'm trying to do something in Google Sheets, and I'm running into some problems.
Table A is what I have with formulas. I populated tables B and C myself, but I would like (one of) them to be generated from table A or (even better) from scratch, based on the data in A and B, and in the style of table C by preference.
| A | B | C | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| Lvl1 | Lvl2 | Lvl3 | Lvl1 | Lvl2 | Lvl3 | Lvl1 | Lvl2 | Lvl3 | ||
| 108 | 27 | 6,75 | 108 | 27 | 6,75 | 108 | ||||
| 108 | 27 | 4,5 | 4,5 | 27 | ||||||
| 108 | 27 | 15,75 | 15,75 | 6,75 | ||||||
| 108 | 18 | 4,5 | 18 | 4,5 | 4,5 | |||||
| 108 | 18 | 3 | 3 | 15,75 | ||||||
| 108 | 18 | 10,5 | 10,5 | 18 | ||||||
| 108 | 63 | 15,75 | 63 | 15,75 | 4,5 | |||||
| 108 | 63 | 10,5 | 10,5 | 3 | ||||||
| 108 | 63 | 36,75 | 36,75 | 10,5 | ||||||
| 72 | 18 | 4,5 | 72 | 18 | 4,5 | 63 | ||||
| 72 | 18 | 3 | 3 | 15,75 | ||||||
| 72 | 18 | 10,5 | 10,5 | 10,5 | ||||||
| 72 | 12 | 3 | 12 | 3 | 36,75 | |||||
| 72 | 12 | 2 | 2 | 72 | ||||||
| 72 | 12 | 7 | 7 | 18 | ||||||
| 72 | 42 | 10,5 | 42 | 10,5 | 4,5 | |||||
| 72 | 42 | 7 | 7 | 3 | ||||||
| 72 | 42 | 24,5 | 24,5 | 10,5 | ||||||
| 252 | 63 | 15,75 | 252 | 63 | 15,75 | 12 | ||||
| 252 | 63 | 10,5 | 10,5 | 3 | ||||||
| 252 | 63 | 36,75 | 36,75 | 2 | ||||||
| 252 | 42 | 10,5 | 42 | 10,5 | 7 | |||||
| 252 | 42 | 7 | 7 | 42 | ||||||
| 252 | 42 | 24,5 | 24,5 | 10,5 | ||||||
| 252 | 147 | 36,75 | 147 | 36,75 | 7 | |||||
| 252 | 147 | 24,5 | 24,5 | 24,5 | ||||||
| 252 | 147 | 85,75 | 85,75 | 252 | ||||||
| 63 | ||||||||||
| 15,75 | ||||||||||
| 10,5 | ||||||||||
| 36,75 | ||||||||||
| 42 | ||||||||||
| 10,5 | ||||||||||
| 7 | ||||||||||
| 24,5 | ||||||||||
| 147 | ||||||||||
| 36,75 | ||||||||||
| 24,5 | ||||||||||
| 85,75 | ||||||||||
The formulas:
Table A, lvl1:
=ARRAYFORMULA(MAKEARRAY(COUNTA(B3:B14)^3;1;LAMBDA(r;c;INDEX(B3:B14;1+QUOTIENT(r-1;COUNTA(B3:B14)^2))*$A$3)))
Lvl2:
=ARRAYFORMULA(MAKEARRAY(COUNTA(B3:B14)^3;1;LAMBDA(r;c;INDEX(B3:B14;1+QUOTIENT(MOD(r-1;COUNTA(B3:B14)^2);COUNTA(B3:B14)))/SUM(B3:B14)*INDEX(B3:B14;1+QUOTIENT(r-1;COUNTA(B3:B14)^2))*$A$3)))
Lvl2:
=ARRAYFORMULA(MAKEARRAY(COUNTA(B3:B14)^3;1;LAMBDA(r;c;INDEX(B3:B14;1+MOD(r-1;COUNTA(B3:B14)))/SUM(B3:B14)*INDEX(B3:B14;1+QUOTIENT(MOD(r-1;COUNTA(B3:B14)^2);COUNTA(B3:B14)))/SUM(B3:B14)*INDEX(B3:B14;1+QUOTIENT(r-1;COUNTA(B3:B14)^2))*$A$3)))
My objective is to create a treelike structure with calculations on 3 levels.
I have a set of 2 to 12 elements with n values from 1 to 11 (3, 2 and 7 in the example sheet). These are to be multiplied by a given value, the multiplier. This gives us our level 1.
Step 1:
Each of the n values has to be divided in as many as there are elements in the total set (divisions following the ratio of each of the original elements to their total (level 2 has n² values).
Step 2:
Same principle, but for the level 3 (n³ values).
Step 3:
Presenting these values in a visual treelike structure like in B or even better, C (jumping a line when jumping levels)
or
Manipulating the data in A to present it like B or (better) C