Hi,
I have a series of numbers for example 1,2,33,23,80,73.
I want to know all the result these numbers add together.

is there a way?

One way I know of is to try an awesome formula by Bob Phillips,
re: http://tinyurl.com/9on4z

Adapted .. here's how it goes

Assume you have in A1: 1,2,33,23,80,73
Enter the delimiter into B1: , (type a comma)

Then .. put Bob's formula into say, C1:

=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1)))),
FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+2))))
-FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1))))-1))

C1 will return the sum of the numbers in A1: 212

(Just don't ask me how it works, I dunno <bg>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"quietning" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi,
> I have a series of numbers for example 1,2,33,23,80,73.
> I want to know all the result these numbers add together.
>
> is there a way?
>

Thanks a lot.

The formula is so complex.

Hi

I'm not sure if I understand your question, but my interpretation is that
you want to know what each combination of 2 numbers added together is.

If that is the case then try the following.
Enter the series of numbers in cells A1:A6
In cell B1 enter
=($A1+OFFSET($A1,COLUMN()-1,0))*--(COLUMN()<8-ROW())
Copy this formula across through B1:F1
Copy B1:F1 down through B2:B6

The resulting matrix will give you each combination of adding any two of the
numbers in your series.


Regards

Roger Govier


quietning wrote:
> Hi,
> I have a series of numbers for example 1,2,33,23,80,73.
> I want to know all the result these numbers add together.
>
> is there a way?
>

Roger,

In fact I want to know EVERY POSSIALBE RESULT of that series of numbers
add.

For example if I have 1,3,10,8
I want to get the result of:

1+3,1+10,1+18,2+10,3+8,10+8
1+3+10,1+3+8,1+10+8,3+10+8
1+3+10+8

Is that possible?

You're welcome !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Max wrote...
>One way I know of is to try an awesome formula by Bob Phillips,
>re: http://tinyurl.com/9on4z
>
>Adapted .. here's how it goes
>
>Assume you have in A1: 1,2,33,23,80,73
>Enter the delimiter into B1: , (type a comma)
>
>Then .. put Bob's formula into say, C1:
>
>=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
>ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1)))),
>FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
>ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+2))))
>-FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
>ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1))))-1))
....

I've always disliked parsing approaches that use dummy characters as
placeholders. I also dislike the B1&A1&B1 because it involves an
unnecessary concatenation.

If this formula would be used a lot, then there's cause to use a
defined name like seq referring to =ROW(INDIRECT("1:1024")). Then you
could use the array formula

=SUM(IF(MID(B1&A1,seq,1)=B1,--MID(A1,FIND(B1,B1&A1,seq),
FIND(B1,A1&B1,seq+1)-FIND(B1,B1&A1,seq))))

to sum the numbers in the string. Without the defined name, it becomes

=SUM(IF(MID(","&A1,ROW(INDIRECT("1:1024")),1)=",",--MID(A1,
FIND(",",","&A1,ROW(INDIRECT("1:1024"))),
FIND(",",A1&",",ROW(INDIRECT("2:1025")))
-FIND(",",","&A1,ROW(INDIRECT("1:1024"))))))

As for the OP's follow-up asking for a way to calculate the sums of all
subsequences of the sequence in A1, it can be done with formulas, but
it's MESSY & INEFFICIENT. But when have I ever let that stop me?

Step 1: parse the sequence in A1 into single numbers in separate cells
using Data > Text to Columns, and name the resulting range nums. For
the OP's example, this would be 6 numbers.

Step 2 Select a 63 (2^6-1) row by 1 column range and enter the array
formula

=MMULT(MOD(INT(ROW(INDIRECT("1:63"))/2^{5,4,3,2,1,0}),2),
TRANSPOSE(nums))

... Sheer brilliance (grossly understated) !
2 marvellous solutions in one masterful response ..

Thanks, Harlan !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi Harlan Grove,

You solution is impressive!
Great job you did!

Hi Harlan

Just to add to the other responses, this is fantastic.
I guessed if anyone was going to come up with a solution, it would be you!
It makes my attempt at answering the question seem so feeble.

Regards

Roger Govier


Harlan Grove wrote:
> Max wrote...
>
>>One way I know of is to try an awesome formula by Bob Phillips,
>>re: http://tinyurl.com/9on4z
>>
>>Adapted .. here's how it goes
>>
>>Assume you have in A1: 1,2,33,23,80,73
>>Enter the delimiter into B1: , (type a comma)
>>
>>Then .. put Bob's formula into say, C1:
>>
>>=SUMPRODUCT(--MID(A1,FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
>>ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1)))),
>>FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
>>ROW(INDIRECT("2:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+2))))
>>-FIND("~",SUBSTITUTE(B1&A1&B1,B1,"~",
>>ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))+1))))-1))
>
> ...
>
> I've always disliked parsing approaches that use dummy characters as
> placeholders. I also dislike the B1&A1&B1 because it involves an
> unnecessary concatenation.
>
> If this formula would be used a lot, then there's cause to use a
> defined name like seq referring to =ROW(INDIRECT("1:1024")). Then you
> could use the array formula
>
> =SUM(IF(MID(B1&A1,seq,1)=B1,--MID(A1,FIND(B1,B1&A1,seq),
> FIND(B1,A1&B1,seq+1)-FIND(B1,B1&A1,seq))))
>
> to sum the numbers in the string. Without the defined name, it becomes
>
> =SUM(IF(MID(","&A1,ROW(INDIRECT("1:1024")),1)=",",--MID(A1,
> FIND(",",","&A1,ROW(INDIRECT("1:1024"))),
> FIND(",",A1&",",ROW(INDIRECT("2:1025")))
> -FIND(",",","&A1,ROW(INDIRECT("1:1024"))))))
>
> As for the OP's follow-up asking for a way to calculate the sums of all
> subsequences of the sequence in A1, it can be done with formulas, but
> it's MESSY & INEFFICIENT. But when have I ever let that stop me?
>
> Step 1: parse the sequence in A1 into single numbers in separate cells
> using Data > Text to Columns, and name the resulting range nums. For
> the OP's example, this would be 6 numbers.
>
> Step 2 Select a 63 (2^6-1) row by 1 column range and enter the array
> formula
>
> =MMULT(MOD(INT(ROW(INDIRECT("1:63"))/2^{5,4,3,2,1,0}),2),
> TRANSPOSE(nums))
>