find combination of cells that equal a sum
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. -- Billy Rogers Dallas,TX Currently Using SQL Server 2000, Office 2000 and Office 2003 http://thedataguru.blogspot.com/ |
find combination of cells that equal a sum
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/for...lve_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. |
All times are GMT +1. The time now is 05:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com