Formula help

[M.Young]

I have a column with 20 rows of data (varying dollar amounts). The total of the 20 rows = $10,066.82. I have two different dollars amounts ($4,879.25 and $5,187.57 which are sub-totals of the 20 rows)that I have to identify the # of rows which add up to the $4,879.25 and the # rows that add up to the $5,187.57. Can this be done in a formula??

 

[Jay Petrulis]

Hi All,

For those interested, a cumulative combination function which has a variable reference is as follows:

=1+SUM(COMBIN(A1,ROW(INDIRECT("1:"&A1))))
array entered and where A1 is the reference cell.

A1 must be a number >=1 for this to work, else Excel returns a #REF! error.

Bye,
Jay

 

[Jay Petrulis]

Last post on this (I promise)

2^n or 2^n - 1 returns the combinatorial results (depending if 0 is in the set). Disregard the array formula.

The OP issue requires brute force, as variations of this problem in fast time are akin to the holy grail of computer science.

You may be able to do it using the SOLVER, and a some tricky programming in a UDF would probably do it as well. Very difficult.

Bye,
Jay

 

[Chris Davison]

I have VBA which will find any permutation of numbers given a target value

so if your target is $ 5,995.37 it will find the 4 or 5 or 6 or however many numbers from a list add up to this total

emial me and I'll send it, I find it very useful at bank recs

it's currently configured for any 6 numbers from a sample, but in theory can be augmented to look for any amount, at the trade-off for time : one of the previous posters pointed out the 1million+ permutations which is absolutely correct

any 6 from a sample of 100 takes around 1/2 hour

let me know and I'll send it

Chris

novulari@hotmail.com

 

[Aladin Akyurek]

On 2002-03-20 15:10, Jay Petrulis wrote:
Hi Abdc,

If the value cannot be obtained, that is equivalent to COMBIN(x,0), which is always 1, so change

{ =SUM(COMBIN(20,ROW(1:20))) }
= 1,048,575

to

{ =1+SUM(COMBIN(20,ROW(1:20))) }
=1,048,576

Just an exercise, as your point is very good.

Can anybody help to generalize this by making a reference to a cell rather than hardcoding the 20 in the formula?

This doesn't work (G1 is the reference cell)
=1+SUM(COMBIN(G1,INDIRECT("ROW(1:"&G1&")")))
array entered.

Any help greatly appreciated.

Thanks,
Jay

Jay,

It's:

{=1+SUM(COMBIN(G1,ROW(INDIRECT("1:"&G1))))}

Aladin

 

[Dave Patton]

2 points re what adds up

- PC Mag had 2 macros AddsUp and AddsUp2

- One could do this with Solver; however, I do not have Solver installed in this PC.

 

[Aladin Akyurek]

On 2002-03-23 10:51, Chris Davison wrote:
I have VBA which will find any permutation of numbers given a target value

so if your target is $ 5,995.37 it will find the 4 or 5 or 6 or however many numbers from a list add up to this total

emial me and I'll send it, I find it very useful at bank recs

it's currently configured for any 6 numbers from a sample, but in theory can be augmented to look for any amount, at the trade-off for time : one of the previous posters pointed out the 1million+ permutations which is absolutely correct

any 6 from a sample of 100 takes around 1/2 hour

let me know and I'll send it

Chris

novulari@hotmail.com

Chris,

Just curious: Did you often do this task manually? If so, how did you go about?

The reason I ask is that people (accountants?) who do this might be using heuristics (shortcuts or rules, say) that must beat brute force approaches in most cases. If those heuristics can be made explicit, maybe a system of formulas might be found to implement them in a spreasheet.

Any comments?

Aladin

 

[Jay Petrulis]

On 2002-03-23 12:43, Aladin Akyurek wrote:

On 2002-03-23 10:51, Chris Davison wrote:
I have VBA which will find any permutation of numbers given a target value

so if your target is $ 5,995.37 it will find the 4 or 5 or 6 or however many numbers from a list add up to this total

emial me and I'll send it, I find it very useful at bank recs

it's currently configured for any 6 numbers from a sample, but in theory can be augmented to look for any amount, at the trade-off for time : one of the previous posters pointed out the 1million+ permutations which is absolutely correct

any 6 from a sample of 100 takes around 1/2 hour

let me know and I'll send it

Chris

novulari@hotmail.com

Chris,

Just curious: Did you often do this task manually? If so, how did you go about?

The reason I ask is that people (accountants?) who do this might be using heuristics (shortcuts or rules, say) that must beat brute force approaches in most cases. If those heuristics can be made explicit, maybe a system of formulas might be found to implement them in a spreasheet.

Any comments?

Aladin

Hi Chris,

I would like to take a look at your workbook. At your convenience, please e-mail the file to

john.petrulis@notes.ntrs.com

Aladin and Chris -- On another forum, Tom Ogilvy has posted some code that may be adapted to this use as well. I've seen a long discussion with Dana DeLouis and Myrna Larson as primaries on this, too.

I will research and post back. I know that Tom's code was almost instantaneous, but it was done on the worksheet (precluding use for the size of the data Chris works with), so a combination of the approaches might make for even better results.

Aside: this original post and subsequent discussion is terrific in my book.

Bye,
Jay

 

[Chris Davison]

On 2002-03-23 12:43, Aladin Akyurek wrote:

On 2002-03-23 10:51, Chris Davison wrote:
I have VBA which will find any permutation of numbers given a target value

so if your target is $ 5,995.37 it will find the 4 or 5 or 6 or however many numbers from a list add up to this total

emial me and I'll send it, I find it very useful at bank recs

it's currently configured for any 6 numbers from a sample, but in theory can be augmented to look for any amount, at the trade-off for time : one of the previous posters pointed out the 1million+ permutations which is absolutely correct

any 6 from a sample of 100 takes around 1/2 hour

let me know and I'll send it

Chris

novulari@hotmail.com

Chris,

Just curious: Did you often do this task manually? If so, how did you go about?

The reason I ask is that people (accountants?) who do this might be using heuristics (shortcuts or rules, say) that must beat brute force approaches in most cases. If those heuristics can be made explicit, maybe a system of formulas might be found to implement them in a spreasheet.

Any comments?

Aladin

Sorry for the delay... I must have missed the follow ups...

I use this each Monday morning and once a month. The commenest problem area is the grouping of sales via credit cards or debit cards. So we have 7 sales hitting the bank statement vs only 2 sales processed on the accounting system.

(Obviously, the flaw here is that the person who processes them needs to align their processing to what it actually happening out there on the sales terminals, but this is outside my control!)

Luckily, my problem revolves around the date : the grouping of the sales by date, so I have an indirect help in so far as I can use my judgement (via a pivot table) with the dates.

From the project's inception, I demanded that the processing and banking always include a narrative at the end : "MMDD" so I could later interrogate and group by it.

Heuristically : I sum amounts based on identical MMDD endings

so VISA0126 $ 1450 = VISA0126 $ 1450

and "assume" that :

VISA0323 $ 5500 = AMEX0323 $ 5500

(operator input error on card type)


Also there is the "transposition" rule :

Sales is $ 358

Operator keys $ 538

difference = $180

if the difference is divisible by "9", there's a strong possibility someone has transposed 2 digits somewhere.

Translated to formula : hmmmm...!

if(mod(suspect1-suspect2,9)=0) then proceed


I also, if still stumped, assume net values have been keyed rather than gross (after TVA). Sometimes this helps.

Formula along the lines of above but (/117.5)*100


Also as a sub-layer to all of these, I sometimes carry out the above 1st on credits then on debits, on the basis that operators often process in batches : batches of credits in the morning, batches of debits in the afternoon (for example)

(batches of rejected credit cards, batches of subscription payments, by date)

I *do* have spreadhseets which deal with the *type* of problems for each bank.... each attempts to replicate that heuristic logic of the problem.... it's interesting that you can sort of second-guess the mathematics of the problem based on what kind of environment you are trying to reconcile within... mine have very definable borders : cheque accounts, income accounts, credit card accounts, membership database accounts - each seems to throw up different kind of imbalance scenarios, and thusly, maybe, manageable imbalance solutions

The perfect answer is to *ensure* that what is processed is the same as what hits the bank : but with different companies, different staff, different priorities, systems, skills and different commitment levels, it's practically impossible

Hence the fire-fighting !




I use all these, plus the VBA code from this board.

 

[Chris Davison]

On 2002-03-23 12:59, Jay Petrulis wrote:
I've seen a long discussion with Dana DeLouis and Myrna Larson as primaries on this, too.

I will research and post back.Jay

cool.... I wouldn't mind taking a look at that discussion Jay.

There's that old addage about "if it's watertight, accounts staff will find a way round it to make it leak" and it never ceases to amaze me the different type of errors people come up with. I'm sure you can sort of equate this to the underlying principles of choas theory !

I'll email you the spreadsheet, as well as posting the code in this message.

Note to anyone who pastes this code into their speadsheet : you have to highlight your range of numbers before invoking the macro

Sub SumCertain()
Dim a(100) As Double
Dim Targt As Double
Targt = InputBox("Enter Target")
Sol1 = ""
n = 0
For Each cell In Selection
n = n + 1
a = cell.Value
If Abs(a - Targt) < 0.01 Then Sol1 = Sol1 & a & Chr(10)
Next

MsgBox Sol1, vbOKOnly, "Solutions with 1 Variable"

Sol2 = ""
For r = 1 To n
For s = 1 To n
If r = s Then GoTo nxt2
If a(r) + a(s) = Targt Then Sol2 = Sol2 & a(r) & "+" & a(s) & Chr(10)
nxt2:
Next
Next

MsgBox Sol2, vbOKOnly, "Solutions with 2 Variables"

Sol3 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
If r = s Then GoTo nxt3
If r = t Then GoTo nxt3
If s = t Then GoTo nxt3
If a(r) + a(s) + a(t) = Targt Then Sol3 = Sol3 & a(r) & "+" & a(s) & "+" & a(t) & Chr(10)
nxt3:
Next
Next
Next

MsgBox Sol3, vbOKOnly, "Solutions with 3 Variables"

Sol4 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
For u = 1 To n
If r = s Then GoTo nxt4
If r = t Then GoTo nxt4
If r = u Then GoTo nxt4
If s = t Then GoTo nxt4
If s = u Then GoTo nxt4
If t = u Then GoTo nxt4
If a(r) + a(s) + a(t) + a(u) = Targt Then Sol4 = Sol4 & a(r) & "+" & a(s) & "+" & a(t) & "+" & a(u) & Chr(10)
nxt4:
Next
Next
Next
Next

MsgBox Sol4, vbOKOnly, "Solutions with 4 Variables"

Sol5 = ""
For r = 1 To n
For s = 1 To n
For t = 1 To n
For u = 1 To n
For v = 1 To n
If r = s Then GoTo nxt5
If r = t Then GoTo nxt5
If r = u Then GoTo nxt5
If r = v Then GoTo nxt5
If s = t Then GoTo nxt5
If s = u Then GoTo nxt5
If s = v Then GoTo nxt5
If t = u Then GoTo nxt5
If t = v Then GoTo nxt5
If u = v Then GoTo nxt5
If a(r) + a(s) + a(t) + a(u) + a(v) = Targt Then Sol5 = Sol5 & a(r) & "+" & a(s) & "+" & a(t) & "+" & a(u) & "+" & a(v) & Chr(10)
nxt5:
Next
Next
Next
Next
Next

MsgBox Sol5, vbOKOnly, "Solutions with 5 Variables"

End Sub


If anyone recognises this code, please let me know as I've been dying to pay thanks to the author !!!

Chris