Re: finding differences in a list of amounts -- MIDRANGE-L

Hey, db, you are the wild man!

I assume the file behind the Excel total is the same as the one behind the report. I've been thinking a little about how to get all these combinations. Considered recursive solutions, etc.

A couple ideas:

1. Look at the processes instead of the data. What is the code that generates the report? Can you compare it to that which gives the total in the spreadsheet? What is different, what selection assumptions are made, etc.?

2. If you need to do the brute force thing, consider that you can eliminate a lot of the data. E.g., The only valid values are those < the discrepancy. And once you've picked a value for testing, the only ones valid for combining with the test value are those < discrepancy - test value, etc. You see the recursive thing coming forth? Loops might be tough, as you don't know the number of elements.

3. But this does not sound like fun to code (well, actually, it does!). But maybe you can partitition the data into buckets that give you candidates for summing to the discrepancy.

Nothing in SQL seems easily to fit this problem. Probably real code needed. It could severely cripple a machine if not bounded by the kinds of thing I discuss above. Consider that the number of combinations for n values is (2 ^ n) - 1, I think

But still don't have a good answer - analysis of process here seems the better route, rather than brute force computation that'd take a lot to develop and could be taxing to run. Maybe APL? Ducking!!!

HTH
Vern

At 01:24 PM 9/17/2004, you wrote:

(Cross posting to midrange-l, rpg400-l)

Anybody know of a solution for this?

I have a user that tells me that a report she is getting has the wrong total
amount.  The report shows the detail (a lot of it) but her total that she
keeps track of in a spreadsheet is different from the report's.  Her
spreadsheet doesn't have the detail.  Here's what I have:

$384,582.91 = the report total
$371,448.26 = the user's total
-----------
$ 13,134.65 = difference

Unfortunately, I do not have a single amount of 13,134.65 in my detail.  So,
I must have multiple amounts that total to 13,134.65.

The solution I am looking for is to be able to try all the combinations in
the detail to see what turns up equal to a given total amount.  For example,
if I have the following detail:
     185.69
  11,134.60
     500.03
   4,841.65
   1,500.02
     419.85
etc.
A good tool would be able to find that the 2nd, 3rd, and 5th amounts add up
to the total amount I'm looking for.

Does Excel have a function for something like this?  Or is there to do it on
the 400?  SQL functions?

tia,
db

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.