Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Formula help

  1. #11
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #12
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #13
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  4. #14
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  5. #15
    Board Regular
    Join Date
    Feb 2002
    Location
    Calgary, Alberta Canada
    Posts
    3,428
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default


    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.


  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

  7. #17
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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



  8. #18
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  9. #19
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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(n) = cell.Value
    If Abs(a(n) - Targt) < 0.01 Then Sol1 = Sol1 & a(n) & 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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •