Film, Media & TV3 mins ago
I need an Excel genius for this....
13 Answers
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.
Answers
Best Answer
No best answer has yet been selected by bond. Once a best answer has been selected, it will be shown here.
For more on marking an answer as the "Best Answer", please visit our FAQ.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.
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.
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
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
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
-- answer removed --
Related Questions
Sorry, we can't find any related questions. Try using the search bar at the top of the page to search for some keywords, or choose a topic and submit your own question.