hi, Billy !
A friend of mine asked me this question and I have no idea how to do this
Would there by chance be a function in Excel where you can choose a range of cells with an amount in each cell
and see if any combination of those cells added up to a certain dollar amount?
Say, I have a range of data that adds up to $1,536,211.26
but I'm trying to see if any of the cells in that range add up to $12,455.98.
you could use solver and code to get an (acceptable ?) approach i.e.
(I suggest to assign/define names either static or dynamic to the following data ranges example):
1) Values to the values range NO Titles (say column A)
2) Filter to the next column same rows (say column B)
(code will let 1's / 0's to build a values combination that meets the total amount
3) Target to some cel (say D1) to store/change/... the amount to meet
4) Result to other cell (say D2) for the code loop until equals to Target
formula in this cell: =sumproduct(values,filter)
you can use autofilter to the "Filter" range and/or use conditional format to identify 1's / '0's
now, before use the code, set a reference in your vba project to solver library (SOLVER.XLA not .DLL)
and RUN in the GUI any solver operation (just in case any error when solver is first time used by code)
NOTE: if your excel version is 2002 and up, you will need to change in the code
any reference to solver commands without the "ER" suffix
SolverReset -> SolvReset
SolverOk -> SolvOk (and so on...)
Sub Locate_amount()
Application.ScreenUpdating = False
SolverReset
SolverOk SetCell:="" & [Result].Address & "", _
MaxMinVal:=3, _
ValueOf:="" & [Target] & "", _
ByChange:="" & [Filter].Address & ""
SolverAdd CellRef:="" & [Filter].Address & "", _
Relation:=5, _
FormulaText:="Binary"
SolverOptions Precision:=0.0000001, _
Convergence:=0.001
SolverOk SetCell:="" & [Result].Address & "", _
MaxMinVal:=3, _
ValueOf:="" & [Target] & "", _
ByChange:="" & [Filter].Address & ""
SolverSolve UserFinish:=True
End Sub
you can also download some samples (after suscribe to):
http://www.xl-logic.com/xl_files/formulas/solve_linear.zip
http://www.xl-logic.com/xl_files/formulas/solver.zip
also tutorials and examples (after subscribe to):
http://www.solver.com/suppxlsguide.htm
if any doubts (or further information)... would you please comment ?
hth,
hector.
