Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
alexxandra pe Simpatie.ro
 | Femeie
24 ani
Timis
cauta Barbat
26 - 59 ani |
|
|
TRaP
Moderator
Inregistrat: acum 8 ani
Postari: 951
|
|
I want to sum some values as follows:
- sum cells H36:H38
- sum cells H39:H41
- sum cells H42:H47
So a 3-3-6 row approach.
Expand this for the next to columns.
The approach can be either a formula to drag down and right, or a spill formula.
1. Classic formula working in all Excel versions - drag down and right
3-3-6 row approach
=SUM(OFFSET(H$36; CHOOSE(ROW(A1); 0; 3; 6); 0; CHOOSE(ROW(A1); 3; 3; 6); 1)) |
First formula below anchors the formula above to just the 1 cell - H36 - and spills the correct results.
2. Modern formulas - spill
3-3-6 row approach:
=MAKEARRAY(3; 3; LAMBDA(r;c;
SUM(OFFSET(H36; CHOOSE(r; 0; 3; 6); c - 1; CHOOSE(r; 3; 3; 6); 1))
))
3-6-3 row approach:
=MAKEARRAY(3; 3; LAMBDA(r;c;
SUM(OFFSET(H36; CHOOSE(r; 0; 3; 9); c - 1; CHOOSE(r; 3; 6; 3); 1))
))
6-3-3 row approach:
=MAKEARRAY(3; 3; LAMBDA(r; c;
SUM(OFFSET(H36; CHOOSE(r; 0; 6; 9); c - 1; CHOOSE(r; 6; 3; 3); 1))
)) |
The rest are just for the 3-3-6 row approach:
=LET(
grid; H36:J47;
MAKEARRAY(3; 3; LAMBDA(r;c;
CHOOSE(r;
SUM(INDEX(grid; 1; c):INDEX(grid; 3; c));
SUM(INDEX(grid; 4; c):INDEX(grid; 6; c));
SUM(INDEX(grid; 7; c):INDEX(grid; 12; c))
)
))
) |
=LET(
grid; H36:J47;
MAKEARRAY(3; 3; LAMBDA(r;c;
CHOOSE(r;
SUM(INDEX(grid; {1;2;3}; c));
SUM(INDEX(grid; {4;5;6}; c));
SUM(INDEX(grid; {7;8;9;10;11;12}; c))
)
))
) |
=LET(
grid; H36:J47;
MAKEARRAY(3; 3; LAMBDA(r;c;
CHOOSE(r;
SUM(INDEX(grid; SEQUENCE(3;; 1); c));
SUM(INDEX(grid; SEQUENCE(3;; 4); c));
SUM(INDEX(grid; SEQUENCE(6;; 7); c))
)
))
) |
Source: Gemini
|
|
| pus acum 2 saptamani |
|