software

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!

On Sat, 18 Mar 2006 15:51:28 -0800, rwfrench
gt; wrote:

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

You need the Solver AddIn.

If it's not already loaded, go to Tools--gt; 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--gt; 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
__________________________

On Sun, 19 Mar 2006 18:47:37 0000, Richard Buttrey
gt; wrote:

gt;On Sat, 18 Mar 2006 15:51:28 -0800, rwfrench
gt; wrote:
gt;
gt;gt;Hi, I work at a travel agency and I reconcile a corporate credit card that
gt;gt;might list a total charge of $800 by a hotel but in reality we'll have 2
gt;gt;reservations at that hotel for $400 each so the hotel charges $800 and the
gt;gt;credit card statement shows $800 but I reconcile the credit card statement
gt;gt;against the database report which shows 2 payments of $400. I'd like to
gt;gt;build a formula that I can put into conditional formatting that would look
gt;gt;for different cells in column k that add up to a specific total, in this case
gt;gt;$800. The thing is, I'd need the formula to look for up to any given 10
gt;gt;cells that add up to the specific total. It might only be 2 listings that
gt;gt;add up to $800 but it also might be up to 10 listings that add up to $800.
gt;gt;This is a herculian effort to do manually because you may have 3 at $200
gt;gt;each, 1 at $300 and then they apply a credit or refund of -$100 to the $300
gt;gt;room from the $8000 in refunds they owe us, but I have to combine the credit
gt;gt;card report and the refunds due from supplier report in order to account for
gt;gt;all possibilities using credits. We book at hotels all over the world so I
gt;gt;have daily currency rates to deal with, they charge the wrong amounts, their
gt;gt;DBA name is different from the supplier name so while the database report
gt;gt;might show one name the credit card statement will show a different name.
gt;gt;Sometimes they charge the card a month after the guest stayed, and sometimes
gt;gt;our people make mistakes and fax the credit card info to a hotel that is set
gt;gt;up for direct billing so we pay them on the card and send them a check. If
gt;gt;any of you gurus know a formula to help me add the cells for different
gt;gt;combinations to get a specific total I'll send you avirtual case of beer, but
gt;gt;if you know of a formula that would add the cells to give all combinations
gt;gt;that would be be plus/minus 5% of the total since they sometimes charge more
gt;gt;or less I'll send you a virtual keg! Thanks!
gt;
gt;You need the Solver AddIn.
gt;
gt;If it's not already loaded, go to Tools--gt; Add-ins and tick the Solver
gt;Add in.
gt;
gt;Now with your 10 given cells in say A1:A10, (if they're not in a list
gt;one following another then I suggest filtering them first), put zero
gt;in cells B1:B10
gt;
gt;In say D1 enter the formula
gt;
gt;=SUMPRODUCT((A1:A10)*(B1:B10))
gt;
gt;Now you can use the solver add in
gt;
gt;Tools--gt; Solver In the dialog box set the Target Cell to D1, select
gt;the 'Equal To:' option to 'Value' and enter your desired total in the
gt;box.
gt;
gt;Now in the 'By Changing Cells' box, enter B1:B10,
gt;Next to the 'Subject to Constraints' box choose ADD
gt;In the Cell Reference box enter B1:B10
gt;In the small central drop down box choose 'bin' (meaning binary)
gt;Enter OK
gt;Now Hit the 'Solve' button.
gt;
gt;
gt;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
__________________________

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

quot;Richard Buttreyquot; wrote:

gt; On Sun, 19 Mar 2006 18:47:37 0000, Richard Buttrey
gt; gt; wrote:
gt;
gt; gt;On Sat, 18 Mar 2006 15:51:28 -0800, rwfrench
gt; gt; wrote:
gt; gt;
gt; gt;gt;Hi, I work at a travel agency and I reconcile a corporate credit card that
gt; gt;gt;might list a total charge of $800 by a hotel but in reality we'll have 2
gt; gt;gt;reservations at that hotel for $400 each so the hotel charges $800 and the
gt; gt;gt;credit card statement shows $800 but I reconcile the credit card statement
gt; gt;gt;against the database report which shows 2 payments of $400. I'd like to
gt; gt;gt;build a formula that I can put into conditional formatting that would look
gt; gt;gt;for different cells in column k that add up to a specific total, in this case
gt; gt;gt;$800. The thing is, I'd need the formula to look for up to any given 10
gt; gt;gt;cells that add up to the specific total. It might only be 2 listings that
gt; gt;gt;add up to $800 but it also might be up to 10 listings that add up to $800.
gt; gt;gt;This is a herculian effort to do manually because you may have 3 at $200
gt; gt;gt;each, 1 at $300 and then they apply a credit or refund of -$100 to the $300
gt; gt;gt;room from the $8000 in refunds they owe us, but I have to combine the credit
gt; gt;gt;card report and the refunds due from supplier report in order to account for
gt; gt;gt;all possibilities using credits. We book at hotels all over the world so I
gt; gt;gt;have daily currency rates to deal with, they charge the wrong amounts, their
gt; gt;gt;DBA name is different from the supplier name so while the database report
gt; gt;gt;might show one name the credit card statement will show a different name.
gt; gt;gt;Sometimes they charge the card a month after the guest stayed, and sometimes
gt; gt;gt;our people make mistakes and fax the credit card info to a hotel that is set
gt; gt;gt;up for direct billing so we pay them on the card and send them a check. If
gt; gt;gt;any of you gurus know a formula to help me add the cells for different
gt; gt;gt;combinations to get a specific total I'll send you avirtual case of beer, but
gt; gt;gt;if you know of a formula that would add the cells to give all combinations
gt; gt;gt;that would be be plus/minus 5% of the total since they sometimes charge more
gt; gt;gt;or less I'll send you a virtual keg! Thanks!
gt; gt;
gt; gt;You need the Solver AddIn.
gt; gt;
gt; gt;If it's not already loaded, go to Tools--gt; Add-ins and tick the Solver
gt; gt;Add in.
gt; gt;
gt; gt;Now with your 10 given cells in say A1:A10, (if they're not in a list
gt; gt;one following another then I suggest filtering them first), put zero
gt; gt;in cells B1:B10
gt; gt;
gt; gt;In say D1 enter the formula
gt; gt;
gt; gt;=SUMPRODUCT((A1:A10)*(B1:B10))
gt; gt;
gt; gt;Now you can use the solver add in
gt; gt;
gt; gt;Tools--gt; Solver In the dialog box set the Target Cell to D1, select
gt; gt;the 'Equal To:' option to 'Value' and enter your desired total in the
gt; gt;box.
gt; gt;
gt; gt;Now in the 'By Changing Cells' box, enter B1:B10,
gt; gt;Next to the 'Subject to Constraints' box choose ADD
gt; gt;In the Cell Reference box enter B1:B10
gt; gt;In the small central drop down box choose 'bin' (meaning binary)
gt; gt;Enter OK
gt; gt;Now Hit the 'Solve' button.
gt; gt;
gt; gt;
gt; gt;HTH
gt;
gt; Hi,
gt;
gt; I missed your constraint that your numbers were not in adjacent cells.
gt; Hence if you use this method it will be necessary to filter copy them
gt; from your data to a contiguous range like A1:A10.
gt;
gt; I forgot to add that B1:B10 will identify with a '1', which of the 10
gt; cells total your required number.
gt;
gt; Rgds
gt;
gt; __
gt; Richard Buttrey
gt; Grappenhall, Cheshire, UK
gt; __________________________
gt;