Calculate to find a suffix digit

Hi,

Assume in cell A1: 13452678910129

Every even digit should be multiplied b 2

Result: 1 6 4 10 2 12 7 16 9 2 0 2 2 18

All number greater than 9 must be summed - so we'll get:

1 6 4 1 2 3 7 7 9 2 0 2 2 9

If we sum all those 14 digits we get: 55

I am looking for a formula (no UDF nor Macro nor Helper-columns/cells, please) to return IN B1 the digit which will complete the gap between the above 55 and the next tenth (60), In this example it should return: 5

Thanks in advance,
Elm

There must be a better way in terms of the math (and handling 9) but perhaps:

=10-MOD(SUM(INDEX(MOD(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)*(2-MOD(ROW(A1:INDEX(A:A,LEN(A1))),2)),9),0),(LEN(A1)-LEN(SUBSTITUTE(A1,9,"")))*9),10)

Edit: I didn't think it could be done. My hat's off to DO (again).

This would be "better" perhaps:

=MOD(10-MOD(SUMPRODUCT(--MID(TEXT(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1)*(2-MOD(ROW(A$1:INDEX(A:A,LEN(A1))),2)),"00"),{1,2},1)),10),10)

*delimiters to be adjusted per your locale

(and I use the term "better" loosely of course)

EDIT:
further MOD added to capture fact result could be multiple of 10 (ie return 0 rather than 10)

Thanks.

For the example of 55 it returns 5 which is OK but for: 49015420323751 it returns 4 instead of 8 if I'm not mistaken.

4 18 0 2 5 8 2 0 3 4 3 14 5 2

4+9+0+2+5+8+2+0+3+4+3+5+5+2 = 52

60-52=8

Elm

++++++++++++++++++ EDITED ++++++++++++++++++

DO,

Your second formula also returns 4 - I must be mistaken with my claculation - but where ?

It should return 8 though note I did not lock references to A1 in the ROW call in the first formula which you must do if you're copying downwards.

NOTE: I would suggest using the latest version as the prior would not work correctly where total was multiple of 10.

Originally Posted by ElmerS

for: 49015420323751...

DO,

Your second formula also returns 4 - I must be mistaken with my claculation - but where ?

Please post a sample to illustrate - both formulae should return 8.

Did you note my point re: ROW(A$1:INDEX(...)) rather than ROW(A1:INDEX(...)) - two instances thereof.

I was working on something as DO posted his solution...

.. a little longer but seems to work:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER.

Note: I liked the 10-MOD() idea... so I borrowed that part from DO I was initially looking at using CEILING.. but that would require the whole formula doubled in length.

Originally Posted by NBVC

.. a little longer but seems to work:

can't work out if the above is meant to imply my suggestion doesn't... it does... at least for me though I confess I've not tested on 2003 - could you if / when you are able ?

(curious to know if this is another of the odd "complex" precedent issues pre 2007)

EDIT: it's a nesting issue pre XL2007.

Originally Posted by DonkeyOte

can't work out if the above is meant to imply my suggestion doesn't... it does... at least for me though I confess I've not tested on 2003 - could you if / when you are able ?

(curious to know if this is another of the odd "complex" precedent issues pre 2007)

DO, I was not implying anything... I actually didn't try your second solution because I was trying to figure it out without the influence of your formula as much as I could

Your first formula worked for his first case, but not the second case.

Your second formula doesn't get accepted in 2003 (too many nested functions). It marks the first occurance of LEN() as the breaking point.

Thanks for that.

I wonder if perhaps the below might work pre XL2007 (I don't have an install running myself presently)

=MOD(10-MOD(SUMPRODUCT(--MID(TEXT(MID(A1&REPT("0",15-LEN(A1)),ROW(A$1:A$15),1)*(2-MOD(ROW(A$1:A$15),2)),"00"),{1,2},1)),10),10)

(assumes integers <= 15 digits in length)

EDIT:

Originally Posted by NBVC

Your first formula worked for his first case, but not the second case.

The first works for both (assuming the ROW ref to A1 was fixed prior to being copied downwards) though it would not have worked for say 224
(would need the final MOD call which would go above nesting limits)

As bonkers as Elm's questions are (ie the no UDF, no helpers etc) they are generally quite good fun to resolve :geek icon:

Yes, DO, that one works in 2003 and gives 5 and 8, respectively for the 2 samples.

Apologies for the belated edits - I should have refreshed before finalising

Originally Posted by DonkeyOte

As bonkers as Elm's questions are (ie the no UDF, no helpers etc) they are generally quite good fun to resolve :geek icon:

Yeah, but, I just gotta work on shortening up those single cell formulas

I still suspect there's a math based rule that can be applied to further shorten this (significantly) ... I notice that MVP Legg has been casting his beady eye over this thread so I'm kind of hoping for a humdinger....

Why should the result be 9 ?

Please Login or Register  to view this content.

sum of above being 55, no ?

FWIW, I get 5 also with that last code, just like DO's formulas.