I need an Excel genius for this....

[bond]

In Excel I have a list of random numbers, say in column cells A1:A30, I also have another figure say in cell B1. The figure in cell B1 is made up of a total of 2 or more numbers from the cells in A1:A30, but which of those cells in column A make that total in cell B1? I have already googled and found random answers, none of which have worked so far when I tested them. Some people tell me it cannot be done, but I know Excel is very powerful and think it can be done. Any takers? I can't see a "mathematics" section hence I posted here, and it relates to my finance job. Many thanks.

[MarkRae]

Let's see if I've got this straight.

Supposing B1 contains a value of 20. You want to find two cells within the range A1:A30 which contain e.g. 5 and 15. Is that right?

[dr b]

Can you program in Visual Basic or does this need to be done in the spreadsheet only? Writing a macro that does it would be easy.

[bibblebub]

If the sum of only 2 of the random values then make a 30x30 square with those random values as the header row and column and make each entry in the square be the sum of the row header value and column header value. Then use conditional formatting to highlight those cells within the square that match the required number.

[bond]

To dr b, sorry I don't know how to program with VB or use macros, but I am a bit geeky and can learn quick or copy/paste stuff if given the correct instructions. To MarkRae, yes that is correct but my figures in cells A1:A30 are very random, like 6235.29, 125.32 etc, not whole round numbers or multiples etc.

[MarkRae]

Makes no difference. Like dr b said, this is a fairly trivial task in VBA. When does your homework need to be in by...?

[dr b]

Yes, if only 2 numbers - but OP says "2 or more". By the way when I said "easy" I meant conceptually easy, but potentially very time consuming to run.

[factor-fiction]

I've only glanced at this in passing and will read it fully in the morning, but if it helps there is a random function in excel I recall that will generate a random number for you

[bond]

To MarkRae, I would like to know how you do it, no rush. It's not homework, just a genuine question to help me with my work, since my work colleagues seem to be rubbish with Excel (like me, but I want to learn!).

[MarkRae]

Assuming that the value in B1 is made up of the contents of only two cells within the range A1:A30, you set up a for loop which iterates though each cell within the range and adds its value to each of the other cells in the range one after the other until it finds a pair which add up to the value in B1. Do a Google search for Excel VBA range For Each Cell

[bond]

Thanks MarkRae but it could (and most likely) will be made up of more than 2 of the cells in column A, I guess that makes it a lot more complicated. I will google as you suggest. Thanks.

[HowardKennitby]

for the general case you would probably need some sort of nested loop construction with break-out when condition is satisfied.

Begin searching the range for cell value < Target.
If found store cell reference
Subtract number from Target = Difference1

Continue search looking for cell value less than or equal to Difference1
If cell value = Difference1 then store second cell reference
As main condition met exit search.

If cell value < Difference1
store cell reference
subtract cell value from Difference1 = Difference2

continue in this manner until main condition met

[jonesytwotwo]

I think if you go to the cell B1 and select, it should tell you. It will probably say "= sum of (?????) and identify what the cell is consists of.