Can I do this on excel?!

li'l one

I'm not too sure how to explain this, I have a list of numbers and need to take a number from that list that is a combination of two or more of the numbers, it would take me forever(already spent ages on it) to find that combination manually, does anyone know if excel can help me on this?

For example, on the list is:
10.00
24.50
255.00
27.95

And i need to find the numbers that add up to 34.50.

Hope this is clear!

Overheal

Well, you can do the =SUM(x;y) Operator to add up the two numbers.

So in the first four cells, A1-A4, you have those numbers and then your output cell (say, A5) you make A5 =SUM(A1;A4).

But if you have X+Y=Z; and you know what Z is but you need to find X and Y from a list of possible integers - yeah, im not sure if thats doable. Not in Excel anyway.

In programming if you knew it was TWO numbers from a list of undisclosed size, you can iterate through the list somewhat like this

while(sumfound=false){
if(A1+A2 = Sum){
sumound=true;
else add A1 with next value (A2, A3, etc) until the sum is found;
If no other value sums with A1 to make the number youre looking for, go through the list with A2 and do the same thing, etc. etc

I dont even want to think about it really. Its a few pages worth of code at least, and assumes you understand Programming.

li'l one

Thanks Overheal,

I know what Z is but have a list of 25 numbers and need to find X and Y in there!

There's gotta be some kind of programme for this...

Thoie

If it was always going to be the sum of two numbers (X+Y rather than W+X+Y) I have a method of doing it, but it's not simple. I'd love to hear an easier way.

Hope I can explain this well.

I've named a cell "Target", and put the target value in there (34.50 in your example).

Then I've sorted the row of numbers into ascending order (because I'm going to do a vlookup in a minute) in column A.

In column B I've got =target-A2 (copied down to ..A100).
In column C I've got =VLOOKUP(B2,A$2:A$100,1,FALSE) (copied down)

The value in Column C that isn't #N/A is the "pair" to the value in column A.

So in your example your list of numbers will become

A B C
10.00 24.5 24.5
24.50 10 10
27.95 6.55 #N/A
255.00 -220.5 #N/A

Thoie

li'l one wrote: »

Thanks Overheal,

I know what Z is but have a list of 25 numbers and need to find X and Y in there!

There's gotta be some kind of programme for this...

If it's "only" 25 numbers, and again if the total is definitely the sum of 2 numbers, then another alternative is to make a grid with the list of numbers down the left, and the same list across the top

_1_2_3
1 2 3 4
2 3 4 5
3 4 5 6

Where the 1+1 meet use =$A2+B$1 then copy down and across to fill in the grid. Use conditional formatting to highlight your desired target.

li'l one

Thoie wrote: »

If it's "only" 25 numbers, and again if the total is definitely the sum of 2 numbers, then another alternative is to make a grid with the list of numbers down the left, and the same list across the top

_1_2_3
1 2 3 4
2 3 4 5
3 4 5 6

Where the 1+1 meet use =$A2+B$1 then copy down and across to fill in the grid. Use conditional formatting to highlight your desired target.

Thanks Thoie,

Will try your suggestion of making the grid now.

I'm not certain that z is made from just x and y, it could be 3 or 4 numbers together from the list of 25 that I have!

Frustration....

Overheal

From an optimization standpoint doing the whole 25x25 is not as efficient as my method though :cool: Because once your A1 pass is done, you dont need to use A1 for the A2 pass, or A1 or A2 for the A3 pass, etc. so for the A24 pass (if you got that far because 24 and 25 was the sum you needed to find) that pass would only consist of one check: A24+A25; as the other 23 values would have already been ruled out...

Then again this is why its so difficult to keep good grades in programming. Crimeny.

I would try reposting your problem in the programming forum. Its possible someone there with a bit of free time can help you out better with your issue. Its been *ages* since I've done anything in Code. But there are ways a little dosbox program can be made to scan a spreadsheet file to do what you're asking - assuming this is an operation you need to perform often, and is not just a once off.

Thoie

Overheal wrote: »

From an optimization standpoint doing the whole 25x25 is not as efficient as my method though :cool:

Absolutely, and it's not really scalable either. You could write an Excel macro using your pseudocode, but as with my solution it only works for X+Y, and not for W+X+Y.

Sadly sometimes you just need to get things done If this was something I had to do regularly in my job, I'd have gone for a macro. As a once off, the ugly, less efficient method gets the result.

Thoie

li'l one wrote: »

Thanks Thoie,

Will try your suggestion of making the grid now.

I'm not certain that z is made from just x and y, it could be 3 or 4 numbers together from the list of 25 that I have!

Frustration....

One other thing which I'm sure you've thought off - filter out any numbers greater than your target, as they're of no interest to you.

While there are a great many other things I should be doing, if you don't get the result from the grid, and the numbers aren't commercially sensitive, fling the 25 numbers and the target up here and I'll take a look as well.

li'l one

Thoie wrote: »

One other thing which I'm sure you've thought off - filter out any numbers greater than your target, as they're of no interest to you.

While there are a great many other things I should be doing, if you don't get the result from the grid, and the numbers aren't commercially sensitive, fling the 25 numbers and the target up here and I'll take a look as well.

Ok, so here's the 24 (not 25!) numbers. and the target is: 1639.47

53.59
79
138.8
56.74
6
269.98
29.99
60
14
60.99
72.49
40.46
132.48
4.5
168
99.8
149.95
324.5
33
54.54
47
1067
389.91
27

Thanks for taking a look!!!

Overheal

Thoie wrote: »

Absolutely, and it's not really scalable either. You could write an Excel macro using your pseudocode, but as with my solution it only works for X+Y, and not for W+X+Y.

Sadly sometimes you just need to get things done If this was something I had to do regularly in my job, I'd have gone for a macro. As a once off, the ugly, less efficient method gets the result.

I love pseudo-coding macros. The problem was having to deal with compile errors and everything else though. I didnt have the temperament for it.

Overheal

li'l one wrote: »

Ok, so here's the 24 (not 25!) numbers. and the target is: 1639.47

53.59
79
138.8
56.74
6
269.98
29.99
60
14
60.99
72.49
40.46
132.48
4.5
168
99.8
149.95
324.5
33
54.54
47
1067
389.91
27

Thanks for taking a look!!!

I have an idea, and the boss is AFK...

Overheal

Ok, this is one way to find the numbers

I've rigged this spreadsheet so that in column B you put a 1 or 0 (on/off) to add the number to the sum at the bottom corner.

on my first shot I think I was a mere 5 cents off. But I didnt count that

edit: 0.02 cents: 4.5, 149.95, 324.5, 33, 54.54, 1067

Thoie

God, well, it turns out there are 88 unique solutions!

I found this page which had a macro already worked out:
http://www.tushar-mehta.com/excel/templates/match_values/index.html

One of them is

4.5 6 14 27 29.99 33 40.46 47 53.59 54.54 56.74 60 60.99 72.49 79 99.8 132.48 138.8 149.95 168 269.98 324.5 389.91 1067
Listing the other 87 could take some time...

Overheal

Whew. I knew there was someone out there with more geek than me.

Thoie

Overheal wrote: »

Whew. I knew there was someone out there with more geek than me.

Sadly this time it wasn't me I'm supposed to be doing laundry!

It looks like a nice bit of coding. Someday, when I have clean clothes, I want to trawl through it.

li'l one

THANK YOU SO MUCH!!!!

Brilliance!!

Overheal

Thoie wrote: »

Sadly this time it wasn't me I'm supposed to be doing laundry!

It looks like a nice bit of coding. Someday, when I have clean clothes, I want to trawl through it.

well put! :pac: