Forum Discussion
In a column of numbers is there a way i can find if a number of cells add up to a specific amount?
is there a way i can find if a number of cells add up to a specific amount?
An intriguing question. Is there a way for you to spell out the circumstances , the constraints, the number of cells in this hypothetical (or is it real?) column, etc.? But before venturing any further, you could help us help you by being far more detailed in describing the big picture here. What are the "rules" to be followed, if any? Are you speaking of a column of unlimited size, a specified specific amount that could approach infinity (or is there a finite limit?
- I have several datasets in a column of around 20 rows each. I need to find if any of the numbers in one of the datasets adds up to a specific number, 8166.99. I didnt think it was massively complicated but seems it is!!
I believe I know what you're getting at with this request. One way to approach this task is to simplify and then scale it up once a solution has been found.
Let's say there are four numbers: 1, 2 , 3, 4
Goal: Without repeats, I'd like to find all the possible combinations that add up to 5.
For example:
1 and 4
2 and 3
The COMBIN function is used to obtain the number of a possible combinations without a number repeating. If you run COMBIN 4x as such:
=COMBIN(4,1)
=COMBIN(4,2)
=COMBIN(4,3)
=COMBIN(4,4)
Total combinations from all: 16
Approaches to finding a solution:
1. Create a recursive lambda
2. Generate all the possible number combinations (eg. the COMBIN results) for a given set of data and then filter out the ones that add up to expected total.
3. Solver?
Does this summarize your request accurately?
- Yep that summarises exactly what i need. I have looked at Solver and it blew my mind!
I didnt think it was massively complicated but seems it is!!
Well, you have to recall that your original question was entirely open-ended. My question back to you was asking for some constraints, which only now have you begun to give. That makes it less complicated, but your description is still somewhat ambiguous.
I need to find if any of the numbers in one of the datasets [I have several datasets] adds up to a specific number, 8166.99.
The ambiguity in that sentence (I put together portions of two of yours) is this: "if any of the numbers in one of the datasets" could imply that you know which one of the datasets to focus on, but it also leaves open the possibility that you need to find numbers that total to your target from any one dataset of the several datasets...... Clarity on the dimensions of a problem helps a LOT when trying to resolve said problems.
To follow-up on the suggestion of peiyezhu : you could help us help you if you would post a copy of your spreadsheet with these columns of "around 20 rows each." And maybe clarify some of the ambiguity I allude to above. Is it one known column that's the focus, or any of them, with the known target of 8166.99? Post a copy on OneDrive or GoogleDrive and paste a link here that grants access to it.
