Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching a range for a sum...
Dear all - I'm not familiar with accounting jargon but what I need is this:
say I have a range of different numbers from cell A2 to cell A100. I need to establish if any of these cells *ADDED TOGETHER* equal "x". so when I enter the no "x" into the formula in cell A102, this will then highlight which cells will equal "x". An example: I need to establish if any cells added together equal 150. I put in 150 in the formula. Formula then searches the range A2 to A100 and highlights cell A27 which contains no.100, and cell A89 which contains the no. 50. Any assistance will be most sincerely appreciated!! Cheers Bob |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching a range for a sum...
chickenshed_bob Wrote: Dear all - I'm not familiar with accounting jargon but what I need is this: say I have a range of different numbers from cell A2 to cell A100. I need to establish if any of these cells *ADDED TOGETHER* equal "x". so when I enter the no "x" into the formula in cell A102, this will then highlight which cells will equal "x". An example: I need to establish if any cells added together equal 150. I put in 150 in the formula. Formula then searches the range A2 to A100 and highlights cell A27 which contains no.100, and cell A89 which contains the no. 50. Any assistance will be most sincerely appreciated!! Cheers Bob To highlight: Select A2:A100, Formats - Conditional formatting If the current cell is A2 , then formula is =$B2=1, and choose a colour Now use Solver and try the following: B2:B100 = 0 A102 = SUMPRODUCT(A2:A100,B2:B100) In Solver, target cell is A102 equals to value of <your target value by changing cells B2:B100. There should also be another window that reads "Subject to the contraints". In here add a constraint B2:B100 = binary. This limit B2:B100 to be either 1 or 0. Run solver. Here is the catch, if some numbers you know they can appear more than once, those numbers need to appear more than once in your data (ie. A2:A100 in the above example). Another catch is that there may be more than one combination that yields the same target value and solver will not give you all the results. Hope it helps -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=524442 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching a range for a sum...
Morrigan - thankyou for the response. Unfortunatelly, I can't get it to work. In the "solver parameters" I've set the target cell to equal to an amount which I know exists in two of the cells in the range (and the two said cells are the only cells which equal to the amount in the target cell) but it seems unable to work this out. -- chickenshed_bob ------------------------------------------------------------------------ chickenshed_bob's Profile: http://www.excelforum.com/member.php...o&userid=32985 View this thread: http://www.excelforum.com/showthread...hreadid=524442 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching a range for a sum...
Attached is an example using 10 numbers. Hope it helps. +-------------------------------------------------------------------+ |Filename: solver.zip | |Download: http://www.excelforum.com/attachment.php?postid=4552 | +-------------------------------------------------------------------+ -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=524442 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
searching a range for a sum...
Morrigan - thankyou for the response. Unfortunatelly, I can't get it to work. In the "solver parameters" I've set the target cell to equal to an amount which I know exists in two of the cells in the range (and the two said cells are the only cells which equal to the amount in the target cell) but it seems unable to work this out. -- chickenshed_bob ------------------------------------------------------------------------ chickenshed_bob's Profile: http://www.excelforum.com/member.php...o&userid=32985 View this thread: http://www.excelforum.com/showthread...hreadid=524442 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array to named range conversion... | Excel Discussion (Misc queries) | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) | |||
can a formula check for a certain value in a range? | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Searching text in a cell range | Excel Worksheet Functions |