EDITED: Easy way to add a large amount of individual values in tables?

[jenspalmqvist]

EDITED FOR SIMPLIFICATION :

Hello,

I'm not sure how to describe my problem in an easy manner but here goes:

I have three tables, A1:C116, D1:F116 and G1:I116. I want to add the numbers in A1, B1 and C1 to the numbers in D1:116, E1:116 F1:119 respectively in G1:I116, the numbers in A/B/C2 to D/E/F1:116 in C117:232 etc. There is also three constants in J1:L1 that are to be added to G:I.

This didn't seem too daunting at first, until I realised how long it'd take to do that amount of manual input. Is there an easier way to automate this somehow?

Regards,
Jens

[Estevaoba]

Try this in G1, copy to I1 and down:

=SUM(OFFSET(A$1,QUOTIENT(ROWS($A$1:$A1)-1,116),0),D1,J$1)

[FlameRetired]

Jens are you using Tables feature ... requiring table formula syntax ... or are these ranges? How do you want the output. In separate cells or is this a one-off formula you seek?

[jenspalmqvist]

Try this in G1, copy to I1 and down:

=SUM(OFFSET(A$1,QUOTIENT(ROWS($A$1:$A1)-1,116),0),D1,J$1)

Thanks! This formula works for the first round of the loop, but when it should be starting on the A2 loop on row 117 something goes wrong...

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jenspalmqvist]

Jens are you using Tables feature ... requiring table formula syntax ... or are these ranges? How do you want the output. In separate cells or is this a one-off formula you seek?

Aa of right now I'm not using the tables feature. I want the output in separate cells, the output should be for example G1=A1+B1+J1, G2=A1+B2+J1,..., G117=A2+B1+J1, etc.

I realise the G-I columns will be over 30000 rows long, but I need to have the sum of every pair of cells in separate cells...

[jenspalmqvist]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Here you go, columns Z-AC are the ones I'm trying to solve. I hope you understand what I want!

[Estevaoba]

Thanks! This formula works for the first round of the loop, but when it should be starting on the A2 loop on row 117 something goes wrong...

I've made a correction to my formula.
See how it goes.

=SUM(OFFSET(A$1,QUOTIENT(ROWS($A$1:$A1)-1,116),0),OFFSET(D$1,MOD(ROWS($A$1:$A1)-1,116),0),J$1)

[FlameRetired]

Jens I am most confused.

This formula in Z2:AC6 returns what you expect and follows the instructions in column Y. However it stops returning expected results after that.

It appears that starting in row 7 the reference instructions should no longer be Support 2 + Atk/Combat 1 + Atk but are more consistent with

Support 2/Combat 1 + Atk. It's like the "goal posts" suddenly change in row 7.

Here's the formula and what it returns. In Z2:AC16

Please Login or Register  to view this content.

	Y	Z	AA	AB	AC
1	Clara + Trons	Atk	Def	Abil	Lead
2	Support 1 + Atk/Combat 1 + Atk	750	167	176	62013
3	Support 1 + Atk/Combat 2 + Atk	810	167	176	52013
4	Support 1 + Atk/Combat 3 + Atk	810	167	146	62013
5	Support 1 + Atk/Combat 4 + Atk	740	167	171	61013
6	Support 1 + Atk/Combat 5 + Atk	790	167	171	52013
7	Support 2 + Atk/Combat 1 + Atk	740	167	171	61013
8	Support 2 + Atk/Combat 2 + Atk	800	167	171	51013
9	Support 2 + Atk/Combat 3 + Atk	800	167	141	61013
10	Support 2 + Atk/Combat 4 + Atk	730	167	166	60013
11	Support 2 + Atk/Combat 5 + Atk	780	167	166	51013
12	Support 3 + Atk/Combat 1 + Atk	620	167	206	62013
13	Support 3 + Atk/Combat 2 + Atk	680	167	206	52013
14	Support 3 + Atk/Combat 3 + Atk	680	167	176	62013
15	Support 3 + Atk/Combat 4 + Atk	610	167	201	61013
16	Support 3 + Atk/Combat 5 + Atk	660	167	201	52013

What your expected results show is

	Y	Z	AA	AB	AC
1	Clara + Trons	Atk	Def	Abil	Lead
2	Support 1 + Atk/Combat 1 + Atk	750	167	176	62013
3	Support 1 + Atk/Combat 2 + Atk	810	167	176	52013
4	Support 1 + Atk/Combat 3 + Atk	810	167	146	62013
5	Support 1 + Atk/Combat 4 + Atk	740	167	171	61013
6	Support 1 + Atk/Combat 5 + Atk	790	167	171	52013
7	Support 2 + Atk/Combat 1 + Atk	620	167	191	61013
8	Support 2 + Atk/Combat 2 + Atk	610	167	186	60013
9	Support 2 + Atk/Combat 3 + Atk	490	167	221	61013
10	Support 2 + Atk/Combat 4 + Atk	610	167	221	51013
11	Support 2 + Atk/Combat 5 + Atk	490	167	211	60013
12	Support 3 + Atk/Combat 1 + Atk	560	167	226	62013
13	Support 3 + Atk/Combat 2 + Atk	550	167	221	61013
14	Support 3 + Atk/Combat 3 + Atk	430	167	256	62013
15	Support 3 + Atk/Combat 4 + Atk	550	167	256	52013
16	Support 3 + Atk/Combat 5 + Atk	430	167	246	61013

What am I missing?

[jenspalmqvist]

Jens I am most confused.

This formula in Z2:AC6 returns what you expect and follows the instructions in column Y. However it stops returning expected results after that.

It appears that starting in row 7 the reference instructions should no longer be Support 2 + Atk/Combat 1 + Atk but are more consistent with

Support 2/Combat 1 + Atk. It's like the "goal posts" suddenly change in row 7.

Here's the formula and what it returns. In Z2:AC16

Please Login or Register  to view this content.

	Y	Z	AA	AB	AC
1	Clara + Trons	Atk	Def	Abil	Lead
2	Support 1 + Atk/Combat 1 + Atk	750	167	176	62013
3	Support 1 + Atk/Combat 2 + Atk	810	167	176	52013
4	Support 1 + Atk/Combat 3 + Atk	810	167	146	62013
5	Support 1 + Atk/Combat 4 + Atk	740	167	171	61013
6	Support 1 + Atk/Combat 5 + Atk	790	167	171	52013
7	Support 2 + Atk/Combat 1 + Atk	740	167	171	61013
8	Support 2 + Atk/Combat 2 + Atk	800	167	171	51013
9	Support 2 + Atk/Combat 3 + Atk	800	167	141	61013
10	Support 2 + Atk/Combat 4 + Atk	730	167	166	60013
11	Support 2 + Atk/Combat 5 + Atk	780	167	166	51013
12	Support 3 + Atk/Combat 1 + Atk	620	167	206	62013
13	Support 3 + Atk/Combat 2 + Atk	680	167	206	52013
14	Support 3 + Atk/Combat 3 + Atk	680	167	176	62013
15	Support 3 + Atk/Combat 4 + Atk	610	167	201	61013
16	Support 3 + Atk/Combat 5 + Atk	660	167	201	52013

What your expected results show is

	Y	Z	AA	AB	AC
1	Clara + Trons	Atk	Def	Abil	Lead
2	Support 1 + Atk/Combat 1 + Atk	750	167	176	62013
3	Support 1 + Atk/Combat 2 + Atk	810	167	176	52013
4	Support 1 + Atk/Combat 3 + Atk	810	167	146	62013
5	Support 1 + Atk/Combat 4 + Atk	740	167	171	61013
6	Support 1 + Atk/Combat 5 + Atk	790	167	171	52013
7	Support 2 + Atk/Combat 1 + Atk	620	167	191	61013
8	Support 2 + Atk/Combat 2 + Atk	610	167	186	60013
9	Support 2 + Atk/Combat 3 + Atk	490	167	221	61013
10	Support 2 + Atk/Combat 4 + Atk	610	167	221	51013
11	Support 2 + Atk/Combat 5 + Atk	490	167	211	60013
12	Support 3 + Atk/Combat 1 + Atk	560	167	226	62013
13	Support 3 + Atk/Combat 2 + Atk	550	167	221	61013
14	Support 3 + Atk/Combat 3 + Atk	430	167	256	62013
15	Support 3 + Atk/Combat 4 + Atk	550	167	256	52013
16	Support 3 + Atk/Combat 5 + Atk	430	167	246	61013

What am I missing?

I will have to check that out later, I might have made a typo somewhere in the sample sheet maybe?

Anyway, I solved the problem by taking a crash course in VBA macros instead to give me clean (and hopefully correct, will have to proofread that too...) B+N+T formulas in Z:AC.

Thanks for the formula suggestion though, I will take a good look at it if only to learn more about Excel!

[jenspalmqvist]

I've made a correction to my formula.
See how it goes.

=SUM(OFFSET(A$1,QUOTIENT(ROWS($A$1:$A1)-1,116),0),OFFSET(D$1,MOD(ROWS($A$1:$A1)-1,116),0),J$1)

Thank you! I will take a good hard look at that formula later.