Anybody pls help!

  • Thread starter Thread starter quietning
  • Start date Start date
Q

quietning

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>)
 
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
 
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?
 
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))
Click to expand...
....

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 !
 
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
 
Back
Top