Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Formula help

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To get your subtotals are you using SUM or SUBTOTAL. If you are using SUBTOTAL change the function ref to 2
    e.g
    SUBTOTAL(9,C3:C5) will generate a subtotal of the cells C3:C5 using the SUM function
    whereas
    SUBTOTAL(2,C3:C5) will generate a subtotal of the cells C3:C5 using the COUNT function
    (If you want to switch between the 2 then reference the function_ref to another cell which you change from 2 to 9

    If you are just using SUM then use the corresponding COUNT(C3:C5)

    Russell


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    India
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    India
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    (a) Ensure your data is on A2:A21.
    (b) Enter the first Cut of Value in B24
    (c) Cut and paste the following formula in B2
    =IF(A2+B1>=B$24,0,A2+B1)
    (d) Drag (or copy & paste) B2 till B21.
    (e) You should be having a '0' in one of the cells in B2:B21 (say Bn)
    (f) The cells A2:An is your first set and rest the second.
    (g) Change '>=' in (c) to '=' for exact match

    Please tell me if this solved your problem
    - sam

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    These are the 20 different amounts. Some combination of these amounts will equal $4,879.25 and the remainder will equal $5,187.57. It's not as easy as just sub-totaling.
    15.55
    22.42
    58.48
    82.74
    85.18
    172.93
    208.89
    231.20
    244.38
    321.78
    419.80
    499.85
    604.24
    641.53
    869.02
    964.20
    1,008.79
    1,126.70
    1,231.87
    1,257.27


    [ This Message was edited by: M.Young on 2002-03-20 01:20 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How do you calculate the subtotals at the moment? Is it just a number you are given?

    Russell

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default



    [ This Message was edited by: M.Young on 2002-03-20 05:52 ]

  8. #8
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Right now, we calculate it out manually. We get the sub-totaled amounts from another office. Our office has the individual amounts (Bills). I have a report that I have to validate how many individual bills make up the sub-totaled amount given to me by the other office. There are usually other indicative entries on each line that help identify the bills within a sub-total. Ex. Type of bill, age of the bill, etc. In this case, all the indicative data is the same. Thus, we have a serious "Brain Teaser" that we have to sort out. I found the solution manually. It took only 8 hours. Not very efficient. I think this could be done with a formula, maybe not within Excell, but maybe within another Excell freindly software. The solution is:
    $1,008.79
    172.93
    499.85
    58.48
    321.78
    641.53
    15.55
    964.20
    82.74
    869.02
    244.38
    = $4,879.25

    $1,126.70
    1,231.87
    1,257.27
    208.89
    85.18
    231.20
    419.80
    604.24
    22.42
    = $5,187.57

    [ This Message was edited by: M.Young on 2002-03-20 06:15 ]

  9. #9

    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just by way of note, with 20 numbers there are 1,048,575 possible combinations.

  10. #10
    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 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


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
  •