Root Sum,(specially for Pab)

[sangoma123]

Hello friends:

This mail this one specially directed to my friend PAB, to whom I am very glad to greet again.

PAB, since you remember you did a great work with the spreadsheet of Excel, in which you included for me:

AC, Last Digit Sum, Deltas, and Root sum.

The problem that I have discovered is in relation to the "Root sum" .
I know that has not been your failure on having programmed, but my mistake on having explained what I wanted:

For example: In the combination: 5-13-24-29-42, the spreadsheet gives us a value Root, of 32.

This is because the sum:5 + 4 + 6+ 11 + 6= 32.

The problem is that I want that when there appears a number formed by two numbers, like 11, I want reduce it to 2, and then the sum would be of 23, which it is of the form that I it do.




Another example: 5-12-29-31-34 5+3+11+4+7 = 30, which it is the sum that gives us the spreadsheet, but I wanted that number 11, it was turning it into 2(1+1), and the sum would be :5+3+2+4+7=21.

It would be a question of leaving the sums only with numbers of "one number", and providing that if there is a 2 digit number, which reduces it to the only number, before doing the sum.

I imagine that this would be very complicated to do, PAB, but that you are a genius of the Excel, and probably for you do not be so complicated to do.

A lot of thanks for you for anticipated.

Regards

P.D.PAB sorry,I have written Pab,in the tittle of the post,instead PAB.I hope that you do not get angry

 

[PAB]

Hi Sangoma123,

The problem that I have discovered is in relation to the "Root sum" .
I know that has not been your failure on having programmed, but my mistake on having explained what I wanted:

For example: In the combination: 5-13-24-29-42, the SpreadSheet gives us a value Root, of 32.

This is because the sum: 5 + 4 + 6 + 11 + 6 = 32.

The problem is that I want that when there appears a number formed by two numbers, like 11, I want reduce it to 2, and then the sum would be of 23, which it is of the form that I it do.

Give this a try...

=SUMPRODUCT(1+MOD(SUM(O21)-1,9))+SUMPRODUCT(1+MOD(SUM(P21)-1,9))+SUMPRODUCT(1+MOD(SUM(Q21)-1,9))+SUMPRODUCT(1+MOD(SUM(R21)-1,9))+SUMPRODUCT(1+MOD(SUM(S21)-1,9))

Change the cell references to what they actually are.
I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[sangoma123]

Hi again PAB:

I have the 5 numbers combinations in the cells B4-F4.
I have tried to paste the formula into the cell AA ,but Excel dont let me paste it.

This is what I try to paste:

=SUMPRODUCT(1+MOD(SUM(B4)-1,9))+SUMPRODUCT(1+MOD(SUM(C4)-1,9))+SUMPRODUCT(1+MOD(SUM(D4)-1,9))+SUMPRODUCT(1+MOD(SUM(E4)-1,9))+SUMPRODUCT(1+MOD(SUM(F4)-1,9))

Also I have translated the formula to my language,because it´s a very
common problem,but without success when I try to paste it,too.

Is good like I have done the conversion of your formula to my cells B4-F4?


Thanks.

 

[PAB]

Hi Sangoma123,

This is what I try to paste:

=SUMPRODUCT(1+MOD(SUM(B4)-1,9))+SUMPRODUCT(1+MOD(SUM(C4)-1,9))+SUMPRODUCT(1+MOD(SUM(D4)-1,9))+SUMPRODUCT(1+MOD(SUM(E4)-1,9))+SUMPRODUCT(1+MOD(SUM(F4)-1,9))

Also I have translated the formula to my language,because it´s a very
common problem,but without success when I try to paste it,too.

Yes, the formula above works for me in my SpreadSheet once I adjusted the Lotto numbers references.
What language and version of Excel are you using???

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[sangoma123]

Hello PAB:

Do not worry any more.

My Excel is from 2007, and probably that is the problem.

When I introduce the formula 1,9 is highlighted.

I pulse fx, and there appears a screen of "argument of function".

For Number:SUMA (B4)-1,9, Excel needs the divisor in order that I introduce it.

I have tried introducing the 1 as divisor and it accepts the formula, but it does not give the result that I want.

But calm, do not worry any more.

Already I will study the topic to seeing if I manage to solve it calmer.

Thank you very much again for your interest.

Regards

 

[PAB]

Hi Sangoma123,

My Excel is from 2007, and probably that is the problem.

I have just tried and tested the formula in Excel 2007 and it works for me, so I can't understand why it is not working for you .

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[Icewynd]

Hi Sangoma,

I use a lookup table to solve this problem.

=HLOOKUP(B1694,$BF$1:$DB$2,2)

Numbers 1 to 49 in Row 1 BF thru DB
Corresponding root sums in Row 2 BF thru DB

6 drawn Lotto numbers in columns B:G

This would be an alternative if you can't get PAB's SUMPRODUCT formula working.

Good luck!

 

[sangoma123]

Hello PAB and Icewynd:

First,thank you again for your interest.

Yesterday,I posted my problem in an excel forum,and they gave me another formula and it works good for me now.(It´s a little bit more complicated but works fine).

Anyway,a lot of thanks for your quick response.

You are good friends¡¡¡

 

[PAB]

Hi sangoma123,

Yesterday, I posted my problem in an excel forum, and they gave me another formula and it works good for me now (It´s a little bit more complicated but works fine).

Perhaps you could post the formula please so anyone following this thread will benefit.
Thanks in advance.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[sangoma123]

Hello Pab:

I didn´t published the formula because is a little complicated.

Here is :

(Numbers in cells:B4 to F4)



First Step: Cells AC to AG

=SI(LARGO(B4)=2;IZQUIERDA(B4;1)+DERECHA(B4;1);B4).

This for each of the 5 numbers


Second Step: Cells AH to AL

=SI(LARGO(AC4)=2;IZQUIERDA(AC4;1)+DERECHA(AC4;1);AC4).

This for each of the 5 numbers

Third Step: Cell AM

=SUMA(AH4:AL4)


I hope this helps.