+ Reply to Thread
Results 1 to 18 of 18

Mathematical formula / logic to derive few values

  1. #1
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Mathematical formula / logic to derive few values

    Hello friends

    I am trying to figure out a mathematical formula/logic to derive few values. I need to know what formula can give me my desired output.

    Let me tell you what I am trying to find.

    If I have a pool of 80 sequential numbers starting from 1 to 80 and if I need to find all possible cominations of 2 then there would be 3160 combinations =combin(80,2). If I sum up all 3160 combinations, I would get a total for each combination eg. 1+2=3, 1+3=4, 1+4=5 ... so on... until 79+80=159. So if I analyze all totals from 3 to 159, I know that 81 is the average and there are 40 combinations out of 3160 which totals 81. I need to find the value 40 and 81 with the help of a formula. I need to know how many combinations (40) are there out of total combinations that totals up to the average of the sum (81).

    Combinations of 2
    Total Combinations are 3160 (=COMBIN(80,2))
    Min : 3 (1+2)
    Max : 159 (79+80)
    Total : 162 (3+159)
    Avg : 81 (162/2)
    Combinations with 81 as total are 40 (checked manually)
    Conclusion : There are 40 combinations out of 3160 which totals 81

    Combinations of 4
    Total Combinations are 1581580 (=COMBIN(80,4))
    Min : 10 (1+2+3+4)
    Max : 314 (77+78+79+80)
    Total : 324 (10+314)
    Avg : 162 (324/2)
    Combinations with 162 as total are 13442 (checked manually)
    Conclusion : There are 13442 combinations out of 1581580 which totals 162

    I need a formula which can give me an output that will tell me how many combinations (highlighted in blue) are there out of total combinations that totals up to the average of the sum (highlighed in green).
    In first case by supplying a value 2, I need the output as 40 combinations that totals 81.
    In second case by supplying a value 4, I need the output as 13442 combinations that totals 162.

    Here I have given example of 2 and 4 and I need to do this for all combinations of 2 to 10. Also attached a file that has values which I need to get it through formulas. Hope I have explained the question properly. Please help.

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Mathematical formula / logic to derive few values

    I think, macros is preferable here
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Mathematical formula / logic to derive few values

    Thanks @tim201110

    I understand that I can get my desired result by listing all possible combinations but I do not want to do that simply because in excel it is not possible to list all possible combinations of 10 out of a pool of 80.

    Without listing all possible combinations, I wanted is to find out count of combinations that contributes to the highest total after summing up each combination. My attachment has example of combination 2, combination 3 and combination 4. By just looking at the result, I wanted to find out a logic to find my desired result.

    I dont know if it is possible.

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Mathematical formula / logic to derive few values

    What accuracy do you expect?
    Sums of different combinations look like normal distribution.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Mathematical formula / logic to derive few values

    I am not understanding what you wamt tp convey.... I know the distribution is normal.. but I want to find out the count of combinations of the average sum

    If I want to find out
    how many combinations of 4 would be there having their individual sum as 162?
    then I have the answer. Its 13442 combinations because I did that manually.

    If I want to find out
    how many combinations of 5 would be there having their individual sum as 203?
    how many combinations of 10 would be there having their individual sum as 405?
    Here is where I am struggling... I dont even know if it is possible to find out by any logic or formula without listing the combinations.

    I am trying to find a formula/logic that would answer my above questions. This is what I am trying to find.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Mathematical formula / logic to derive few values

    Here is where I am struggling... I dont even know if it is possible to find out by any logic or formula without listing the combinations.
    Same here, because I know there are some problems (like the subset sum problem that seems somewhat similar to the problem here) that are "NP-hard" -- meaning the only rigorous solution is the "brute force list every possible combination and count up the successes" solution.

    If I could suggest a good internet search, I would, because that seems like the easiest way to explore this problem. I have to believe that you are not the first person in the history of mathematics to ask this question. If we could find someone who describes how to find the solution, then you won't have to "reinvent the wheel".

    If it helps, the first problem (pairs that add up to 81) looks like Gauss's add up the first n integers problem looked at from a different angle (http://mathcentral.uregina.ca/QQ/dat...02.06/jo1.html ). When asked to add up the numbers, he recognized that he could add up 1+80, 2+79, etc. In your case, you see that you are trying to find the combinations that add up to n+1, which will be the same combinations. So 40=80/2 should work (at least for the case of n is even like 80).

    The 4 combination case might be similar. I found it interesting that average for pairwise was 81, where the average for groups of 4 is 162=81*2=81*4/2. Does that contribute anything meaningful to developing the algorithm? The average for groups of 3 is 121.5=81*3/2. Does that suggest anything.

    How much help do you need with this? Developing an algorithm and proving that it works (without input from someone who has already fully explored this problem) could be fairly involved. I have looked at it a little bit, and it is an intriguing problem, but I am not sure I am prepared to commit the time needed to develop and prove a complete and rigorous algorithm for this. If I were to take on the problem, I would probably shrink it down (maybe work on the numbers 1 to 10 instead of 1 to 80). Then I would look for patterns in the "subset sums" to see if that suggested something to me.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Mathematical formula / logic to derive few values

    There is a need of small "tune up" of the code in WB post #2.
    1. comment out all inputting lines
    2. change Buffet from string to sum
    3. if Buffet(i)=average then num=num+1
    Last edited by tim201110; 10-24-2017 at 12:26 PM.

  8. #8
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Mathematical formula / logic to derive few values

    Quote Originally Posted by MrShorty View Post
    If it helps, the first problem (pairs that add up to 81) looks like Gauss's add up the first n integers problem looked at from a different angle (http://mathcentral.uregina.ca/QQ/dat...02.06/jo1.html ). When asked to add up the numbers, he recognized that he could add up 1+80, 2+79, etc. In your case, you see that you are trying to find the combinations that add up to n+1, which will be the same combinations. So 40=80/2 should work (at least for the case of n is even like 80).

    The 4 combination case might be similar. I found it interesting that average for pairwise was 81, where the average for groups of 4 is 162=81*2=81*4/2. Does that contribute anything meaningful to developing the algorithm? The average for groups of 3 is 121.5=81*3/2. Does that suggest anything.

    How much help do you need with this? Developing an algorithm and proving that it works (without input from someone who has already fully explored this problem) could be fairly involved. I have looked at it a little bit, and it is an intriguing problem, but I am not sure I am prepared to commit the time needed to develop and prove a complete and rigorous algorithm for this. If I were to take on the problem, I would probably shrink it down (maybe work on the numbers 1 to 10 instead of 1 to 80). Then I would look for patterns in the "subset sums" to see if that suggested something to me.
    It was indeed a very nice article. Yes... average for groups of 4 is 162=81*2=81*4/2 and The average for groups of 3 is 121.5=81*3/2 does make sense. I am breaking my head over it ever since I read your response but unable to come up with something. Then as you suggested, I decided to shrink it to look for patterns so that I can apply to my 1 to 80 problem. Im still unsuccessful. However, I am trying my best... Thank you for your ideas.

    Quote Originally Posted by tim201110 View Post
    There is a need of small "tune up" of the code in WB post #2.
    1. comment all inputting lines
    2. change Buffet from string to sum
    3. if Buffet(i)=average then num=num+1
    I am sorry, but I did not follow what exactly I must do with the code. Can you please elaborate on this?

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Mathematical formula / logic to derive few values

    Not sure that this will be of any help however, working with MrShorty's suggestion of the combinations of numbers 1 through 10 Excel comes up with a formula for the percentage of combinations that will produce the average. The formula is y = 0.0127x^2-0.0635x+0.1619 Attached is the spreadsheet used to produce the formula. The combinations in columns H and over were produced by Mathematica and pasted into the spreadsheet. Excel then counted the instances where the combination summed to equal the average as determined by Gauss's add up the first n integers problem as described by MrShorty.
    Note: I only used the even numbers of elements for the number taken (2,4,6 and 8)
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Mathematical formula / logic to derive few values

    Thanks JeteMc... I viewed it on my phone and it looks very interesting but I want to view it on my desktop for better understanding. I am not keeping too well. I will come back soon. Thanks for your ideas.

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Mathematical formula / logic to derive few values

    There was an error with the x values on the graph, they should simply read 1,2,3,4 (sorry about that).

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Mathematical formula / logic to derive few values

    How about a UDF?

    A
    B
    1
    N
    M
    2
    20
    5
    3
    4
    Sum
    Count
    5
    1
    0
    6
    2
    0
    7
    3
    0
    8
    4
    0
    9
    5
    0
    10
    6
    0
    11
    7
    0
    12
    8
    0
    13
    9
    0
    14
    10
    0
    15
    11
    0
    16
    12
    0
    17
    13
    0
    18
    14
    0
    19
    15
    1
    20
    16
    1
    21
    17
    2
    22
    18
    3
    23
    19
    5
    24
    20
    7
    25
    21
    10
    26
    22
    13
    27
    23
    18
    28
    24
    23
    29
    25
    30
    30
    26
    37
    31
    27
    47
    32
    28
    57
    33
    29
    70
    34
    30
    84
    35
    31
    100
    36
    32
    117
    37
    33
    137
    38
    34
    157
    39
    35
    180
    40
    36
    203
    41
    37
    228
    42
    38
    253
    43
    39
    280
    44
    40
    306
    45
    41
    333
    46
    42
    359
    47
    43
    385
    48
    44
    409
    49
    45
    433
    50
    46
    453
    51
    47
    472
    52
    48
    488
    53
    49
    501
    54
    50
    511
    55
    51
    518
    56
    52
    521
    57
    53
    521
    58
    54
    518
    59
    55
    511
    60
    56
    501
    61
    57
    488
    62
    58
    472
    63
    59
    453
    64
    60
    433
    65
    61
    409
    66
    62
    385
    67
    63
    359
    68
    64
    333
    69
    65
    306
    70
    66
    280
    71
    67
    253
    72
    68
    228
    73
    69
    203
    74
    70
    180
    75
    71
    157
    76
    72
    137
    77
    73
    117
    78
    74
    100
    79
    75
    84
    80
    76
    70
    81
    77
    57
    82
    78
    47
    83
    79
    37
    84
    80
    30
    85
    81
    23
    86
    82
    18
    87
    83
    13
    88
    84
    10
    89
    85
    7
    90
    86
    5
    91
    87
    3
    92
    88
    2
    93
    89
    1
    94
    90
    1


    The array formula in B5:Bxxx and down is

    =TRANSPOSE(CountCombSums(N, M))

    Code to follow.
    Entia non sunt multiplicanda sine necessitate

  13. #13
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Mathematical formula / logic to derive few values

    Credit for the generating function goes to user940 at math.stackexchange: https://math.stackexchange.com/a/836268/

    Please Login or Register  to view this content.
    Calculating CountCombSums(80, 10) takes about a minute.

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Mathematical formula / logic to derive few values

    A couple of improvements makes it 10x faster and gives a 30x smaller memory footprint:

    Please Login or Register  to view this content.
    59 Choose 19 (~ 947 trillion combinations) takes about 8 seconds.
    Last edited by shg; 10-25-2017 at 07:19 PM.

  15. #15
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Mathematical formula / logic to derive few values

    I am down with 100°C temperature but cannot keep myself away from this post. I tried it but it keeps giving me #VALUE! error. I verified the formula i am writing is correct, confirmed with Ctrl Shift Enter but still the same error.

    Can you please attach your file? Thanks

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Mathematical formula / logic to derive few values

    Attached .
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    11-29-2014
    Location
    India
    MS-Off Ver
    MS Excel 2007
    Posts
    240

    Re: Mathematical formula / logic to derive few values

    I simply have no words and I really dont know how to thank you for your wonderful solution. This is just outstanding phenominal exceptional great magneficient superior help you have extended.

    I wonder why I did not consider mathematics as my favorite subject in my school days. Nevertheless... its better late than never. I just want to recover from my fever as fast as I can so that I can better understand the logic and the maths behind this. Thank you so very much. God bless !

  18. #18
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Mathematical formula / logic to derive few values

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. formula to derive time taken for a task in a day
    By vikijain in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-22-2017, 10:16 AM
  2. Derive Values From Inputed Value
    By SalientAnimal in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-02-2017, 12:26 PM
  3. Derive a formula for multiple variables
    By wvr in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2014, 12:16 AM
  4. Derive formula from chart
    By hamidun in forum Excel Charting & Pivots
    Replies: 26
    Last Post: 04-29-2014, 09:12 AM
  5. Derive optimal mixture formula
    By SandPounder1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-12-2014, 05:24 PM
  6. Complex logic formula to delete values
    By randell.graybill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2009, 11:37 PM
  7. derive formula from series of points
    By andyismilesaway in forum Excel General
    Replies: 1
    Last Post: 02-28-2007, 06:55 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1