Forum Discussion
Select the cells for the optimal sum value in Excel
Hello everyone,
It's my first time to post a question in this community. I am looking forward to hearing any advices. Thanks in advance.
My goal is to select the sample IDs with the values, which are added up to be (closer to) a certain number, for instance, 90, in the below table.
The result would be the highlighted ones. Are there any functions or formulas to do so?
Thanks very much.
| Sample ID | # of sample |
| 21-070301-0001 | 18 |
| 21-072163-0001 | 25 |
| 21-072165-0001 | 15 |
| 21-072167-0001 | 20 |
| 21-072169-0001 | 10 |
| 21-072445-0001 | 15 |
| 21-072552-0002 | 20 |
3 Replies
See the attached workbook. You'll have to allow macros.
It's slow and could be improved.
Could you please explain in bit more details, better on sample file, why ID;s in red are close to 90 and other are not. And would you like to select or to highlight?
- Hello Sergei,
Thanks for your reply. I am sorry there might be confusion about the sample ID. Actually, it should be the case ID. For instance, there are 18 samples in case 21-070301-0001. My goal is to select the cases out with the sum of their total number of samples closest to 90 since we are templating the samples into a 96-well microplate (exclude 6 controls).
I would like to create a new list with the selected ones if possible.
Hope it'll make the request clearer.
Thanks.
