Pyramid sum of six digit in single cell

[JAVEDR]

Respected members,

I have six digit number in column A and need pyramid result corresponding cell. For example below -

In A1 if record is 123123 then pyramid calculation result 4 need to be shown in B2

123123​	4​
468477​	6​
245112​	9​

Pyramid calculation below

123123
35435
8978
765
31
4
468477
4214
4635
098
97
6
245112
69623
5585
033
36
9

Thank you in advance for your precious time and feedback.

 

[StephenCrump]

=MOD(SUMPRODUCT((0+MID(A1,{1,2,3,4,5,6},1))*{1,5,10,10,5,1}),10)

 

[JAVEDR]

=MOD(SUMPRODUCT((0+MID(A1,{1,2,3,4,5,6},1))*{1,5,10,10,5,1}),10)

excellent code work like charm

 

[StephenCrump]

Thanks, you can generalise to accommodate lengths other than 6 digits:

=MOD(SUMPRODUCT((0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),COMBIN(LEN(A1)-1,ROW(INDIRECT("1:"&LEN(A1)))-1)),10)

And if you have Excel 365, there is a shorter/better formula.

 

[JAVEDR]

Thanks, you can generalise to accommodate lengths other than 6 digits:

=MOD(SUMPRODUCT((0+MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),COMBIN(LEN(A1)-1,ROW(INDIRECT("1:"&LEN(A1)))-1)),10)

And if you have Excel 365, there is a shorter/better formula.

just one more help can we create that pyramid as well like I shown above.

 

[StephenCrump]

What version of Excel do you have?

 

[JAVEDR]

What version of Excel do you have?

excel 2019

 

[StephenCrump]

	A	B
1	468477	468477
2	04214	04214
3	4635	4635
4	098	098
5	97	97
6	6	6
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=RIGHT(REPT("0",LEN(A1)-1)& SUMPRODUCT(MOD(MMULT(0+MID(A1,{1,2}+ROW(INDIRECT("1:"&LEN(A1)-1))-1,1),{1;1}),10)*10^(LEN(A1)-1-ROW(INDIRECT("1:"&LEN(A1)-1)))),LEN(A1)-1)
B2:B6	B2	=TEXTJOIN(,,MOD(MMULT({1,1},0+MID(B1,{1;2}+SEQUENCE(,LEN(B1)-1,0),1)),10))

B2 formula is for Excel 365

 

[JAVEDR]

	A	B
1	468477	468477
2	04214	04214
3	4635	4635
4	098	098
5	97	97
6	6	6
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=RIGHT(REPT("0",LEN(A1)-1)& SUMPRODUCT(MOD(MMULT(0+MID(A1,{1,2}+ROW(INDIRECT("1:"&LEN(A1)-1))-1,1),{1;1}),10)*10^(LEN(A1)-1-ROW(INDIRECT("1:"&LEN(A1)-1)))),LEN(A1)-1)
B2:B6	B2	=TEXTJOIN(,,MOD(MMULT({1,1},0+MID(B1,{1;2}+SEQUENCE(,LEN(B1)-1,0),1)),10))

B2 formula is for Excel 365

 

[JAVEDR]

Ex

	A	B
1	468477	468477
2	04214	04214
3	4635	4635
4	098	098
5	97	97
6	6	6
Sheet1

Cell Formulas
Range	Formula
A2:A6	A2	=RIGHT(REPT("0",LEN(A1)-1)& SUMPRODUCT(MOD(MMULT(0+MID(A1,{1,2}+ROW(INDIRECT("1:"&LEN(A1)-1))-1,1),{1;1}),10)*10^(LEN(A1)-1-ROW(INDIRECT("1:"&LEN(A1)-1)))),LEN(A1)-1)
B2:B6	B2	=TEXTJOIN(,,MOD(MMULT({1,1},0+MID(B1,{1;2}+SEQUENCE(,LEN(B1)-1,0),1)),10))

B2 formula is for Excel 365

excellent sir perfect what i was looking thanks for your quick revert