calculate various cells to acheive a specific value
Hello,
I am trying to identify two cells within a column that total a specific amount. For example: ColA 546 9732 654 6548 687 9871 I need to find the two (or more cells in some cases) that would create the total of 10,525. For my current task, I have over 100 rows of data and I know two cells equal a specific amount, but I do not know which two. Any help, or assistance given would be greatly appreciated! Jennifer |
calculate various cells to acheive a specific value
You would need to use code or the solver add-in, for the solver add-in using
your example you would create something like this 546 1 9732 1 654 1 6548 1 687 1 9871 1 assume that is A1:B6 (you would put 1s in B1:B6) in C1 put =SUMPRODUCT(A1:A6,B1:B6) in D1 put 10,525 do toolssolver then set target cell $C$1 equal to value of 10525 by changing cells $B$1:$B$6 then click add and use $B$1:$B$6 and from dropdown select bin, click OK then click solve depending on how large (note that the solver that comes with Excel is limited) the range is it can take some time to get a solution, but your example gives this result 546 0 9732 0 654 1 6548 0 687 0 9871 1 where the 1s in B1:B6 show which 2 amounts total 10,525 I believe Harlan Grove wrote VBA code to do this as well, you can Google on that -- Regards, Peo Sjoblom "Jennifer_C" <u35395@uwe wrote in message news:7455401029a02@uwe... Hello, I am trying to identify two cells within a column that total a specific amount. For example: ColA 546 9732 654 6548 687 9871 I need to find the two (or more cells in some cases) that would create the total of 10,525. For my current task, I have over 100 rows of data and I know two cells equal a specific amount, but I do not know which two. Any help, or assistance given would be greatly appreciated! Jennifer |
calculate various cells to acheive a specific value
One way, not necessarily very elegant, is if you've got your data in a
column as your example, copy and paste special in a row across the top of the sheet to form a square table with your original column values, then add values from column A and row 1 to form the square. Conditional format to highlight where the result is 10525, & it shows 9871+654 (or v.v.). -- David Biddulph "Jennifer_C" <u35395@uwe wrote in message news:7455401029a02@uwe... Hello, I am trying to identify two cells within a column that total a specific amount. For example: ColA 546 9732 654 6548 687 9871 I need to find the two (or more cells in some cases) that would create the total of 10,525. For my current task, I have over 100 rows of data and I know two cells equal a specific amount, but I do not know which two. Any help, or assistance given would be greatly appreciated! Jennifer |
calculate various cells to acheive a specific value
for two only
in B1 enter =if(isnumber(match(10525-A1,A:A,0)),10525-A1),"") copy and paste down or you could use the match portion in if true to find where it is. for three, You can set up an NxN table and do something similar for 4 or more it can get very unwieldy. I wrote a macro to this one time, and when it didn't solve quickly, I calculated how long it would take to solve and came up with about a hundred years of computer time. "Jennifer_C" wrote: Hello, I am trying to identify two cells within a column that total a specific amount. For example: ColA 546 9732 654 6548 687 9871 I need to find the two (or more cells in some cases) that would create the total of 10,525. For my current task, I have over 100 rows of data and I know two cells equal a specific amount, but I do not know which two. Any help, or assistance given would be greatly appreciated! Jennifer |
calculate various cells to acheive a specific value
Thank you for the input! It was easy to follow, however, it was taking too
long to complete, and I never got a correct answer. I think it might have been better if I didn't have as much data to work with... Thank you again! Peo Sjoblom wrote: You would need to use code or the solver add-in, for the solver add-in using your example you would create something like this 546 1 9732 1 654 1 6548 1 687 1 9871 1 assume that is A1:B6 (you would put 1s in B1:B6) in C1 put =SUMPRODUCT(A1:A6,B1:B6) in D1 put 10,525 do toolssolver then set target cell $C$1 equal to value of 10525 by changing cells $B$1:$B$6 then click add and use $B$1:$B$6 and from dropdown select bin, click OK then click solve depending on how large (note that the solver that comes with Excel is limited) the range is it can take some time to get a solution, but your example gives this result 546 0 9732 0 654 1 6548 0 687 0 9871 1 where the 1s in B1:B6 show which 2 amounts total 10,525 I believe Harlan Grove wrote VBA code to do this as well, you can Google on that Hello, I am trying to identify two cells within a column that total a specific [quoted text clipped - 18 lines] Jennifer -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200707/1 |
calculate various cells to acheive a specific value
Thanks for the message. This was the way I was able to get my values. Great
help! However, I'll be stuck if I ever need to find three values! David Biddulph wrote: One way, not necessarily very elegant, is if you've got your data in a column as your example, copy and paste special in a row across the top of the sheet to form a square table with your original column values, then add values from column A and row 1 to form the square. Conditional format to highlight where the result is 10525, & it shows 9871+654 (or v.v.). Hello, I am trying to identify two cells within a column that total a specific [quoted text clipped - 18 lines] Jennifer -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200707/1 |
calculate various cells to acheive a specific value
Thanks for the feedback for multiple values! I will need this for looking at
multiples! Very handy! bj wrote: for two only in B1 enter =if(isnumber(match(10525-A1,A:A,0)),10525-A1),"") copy and paste down or you could use the match portion in if true to find where it is. for three, You can set up an NxN table and do something similar for 4 or more it can get very unwieldy. I wrote a macro to this one time, and when it didn't solve quickly, I calculated how long it would take to solve and came up with about a hundred years of computer time. Hello, I am trying to identify two cells within a column that total a specific [quoted text clipped - 16 lines] Jennifer -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200707/1 |
All times are GMT +1. The time now is 11:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com