Formula help
Formula help
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


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
  •  

 

 
DMCA.com