Anybody pls help!
quietning
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?
Max
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" <quietn...@gmail.com> wrote in message
news:1127887659.1...@o13g2000cwo.googlegroups.com...
quietning
The formula is so complex.
Roger Govier
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
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
Harlan Grove
>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))
Max
2 marvellous solutions in one masterful response ..
Thanks, Harlan !
quietning
You solution is impressive!
Great job you did!
Roger Govier
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