Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I find which cells in a dataset equal a specific value | Excel Worksheet Functions | |||
Formula to find equal values with blank cells | Excel Discussion (Misc queries) | |||
How can I find the equal cells in Excel | Excel Discussion (Misc queries) | |||
Find the combination of numbers that when added equal a reqired total?? | Excel Worksheet Functions | |||
To find a combination of numbers that equal a set amount? | Excel Discussion (Misc queries) |