How do I search for a total using nonadjacent cells

[Guest]

Hi, I work at a travel agency and I reconcile a corporate credit card that
might list a total charge of $800 by a hotel but in reality we'll have 2
reservations at that hotel for $400 each so the hotel charges $800 and the
credit card statement shows $800 but I reconcile the credit card statement
against the database report which shows 2 payments of $400. I'd like to
build a formula that I can put into conditional formatting that would look
for different cells in column k that add up to a specific total, in this case
$800. The thing is, I'd need the formula to look for up to any given 10
cells that add up to the specific total. It might only be 2 listings that
add up to $800 but it also might be up to 10 listings that add up to $800.
This is a herculian effort to do manually because you may have 3 at $200
each, 1 at $300 and then they apply a credit or refund of -$100 to the $300
room from the $8000 in refunds they owe us, but I have to combine the credit
card report and the refunds due from supplier report in order to account for
all possibilities using credits. We book at hotels all over the world so I
have daily currency rates to deal with, they charge the wrong amounts, their
DBA name is different from the supplier name so while the database report
might show one name the credit card statement will show a different name.
Sometimes they charge the card a month after the guest stayed, and sometimes
our people make mistakes and fax the credit card info to a hotel that is set
up for direct billing so we pay them on the card and send them a check. If
any of you gurus know a formula to help me add the cells for different
combinations to get a specific total I'll send you avirtual case of beer, but
if you know of a formula that would add the cells to give all combinations
that would be be plus/minus 5% of the total since they sometimes charge more
or less I'll send you a virtual keg! Thanks!

 

[Richard Buttrey]

Hi, I work at a travel agency and I reconcile a corporate credit card that
might list a total charge of $800 by a hotel but in reality we'll have 2
reservations at that hotel for $400 each so the hotel charges $800 and the
credit card statement shows $800 but I reconcile the credit card statement
against the database report which shows 2 payments of $400. I'd like to
build a formula that I can put into conditional formatting that would look
for different cells in column k that add up to a specific total, in this case
$800. The thing is, I'd need the formula to look for up to any given 10
cells that add up to the specific total. It might only be 2 listings that
add up to $800 but it also might be up to 10 listings that add up to $800.
This is a herculian effort to do manually because you may have 3 at $200
each, 1 at $300 and then they apply a credit or refund of -$100 to the $300
room from the $8000 in refunds they owe us, but I have to combine the credit
card report and the refunds due from supplier report in order to account for
all possibilities using credits. We book at hotels all over the world so I
have daily currency rates to deal with, they charge the wrong amounts, their
DBA name is different from the supplier name so while the database report
might show one name the credit card statement will show a different name.
Sometimes they charge the card a month after the guest stayed, and sometimes
our people make mistakes and fax the credit card info to a hotel that is set
up for direct billing so we pay them on the card and send them a check. If
any of you gurus know a formula to help me add the cells for different
combinations to get a specific total I'll send you avirtual case of beer, but
if you know of a formula that would add the cells to give all combinations
that would be be plus/minus 5% of the total since they sometimes charge more
or less I'll send you a virtual keg! Thanks!

You need the Solver AddIn.

If it's not already loaded, go to Tools--> Add-ins and tick the Solver
Add in.

Now with your 10 given cells in say A1:A10, (if they're not in a list
one following another then I suggest filtering them first), put zero
in cells B1:B10

In say D1 enter the formula

=SUMPRODUCT((A1:A10)*(B1:B10))

Now you can use the solver add in

Tools--> Solver In the dialog box set the Target Cell to D1, select
the 'Equal To:' option to 'Value' and enter your desired total in the
box.

Now in the 'By Changing Cells' box, enter B1:B10,
Next to the 'Subject to Constraints' box choose ADD
In the Cell Reference box enter B1:B10
In the small central drop down box choose 'bin' (meaning binary)
Enter OK
Now Hit the 'Solve' button.


HTH




__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

You need the Solver AddIn.

If it's not already loaded, go to Tools--> Add-ins and tick the Solver
Add in.

Now with your 10 given cells in say A1:A10, (if they're not in a list
one following another then I suggest filtering them first), put zero
in cells B1:B10

In say D1 enter the formula

=SUMPRODUCT((A1:A10)*(B1:B10))

Now you can use the solver add in

Tools--> Solver In the dialog box set the Target Cell to D1, select
the 'Equal To:' option to 'Value' and enter your desired total in the
box.

Now in the 'By Changing Cells' box, enter B1:B10,
Next to the 'Subject to Constraints' box choose ADD
In the Cell Reference box enter B1:B10
In the small central drop down box choose 'bin' (meaning binary)
Enter OK
Now Hit the 'Solve' button.


HTH

Hi,

I missed your constraint that your numbers were not in adjacent cells.
Hence if you use this method it will be necessary to filter copy them
from your data to a contiguous range like A1:A10.

I forgot to add that B1:B10 will identify with a '1', which of the 10
cells total your required number.

Rgds

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Guest]

Thanks Richard, I'll give this a shot. I knew there had to be a way of
having it try to sum different cells to look for a total or total range.
There are so many variables in tracking them down and I hope this will bring
order to chaos! Much obliged!

Rodney