1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a series of numbers that appear in any order

Discussion in 'Excel Challenges' started by bines53, Jan 4, 2018.

  1. bines53

    bines53 Active Member

    Messages:
    672
    Hello friends ,

    Find several times, appears in this series of numbers 1,1,1,2 .

    Has a well-known formula, is not flexible enough and convenient in my opinion,

    =SUM(--(MMULT(COUNTIF(H1:K1,A1:D20),{1;1;1;1})=10)),

    Of course, the challenge is to find another formula.

    My formula works only on positive numbers (including 0)

    Conditioning is, without volatile functions.

    Good luck!

    David

    Attached Files:

  2. bines53

    bines53 Active Member

    Messages:
    672
    Hello friends ,


    Another clarification, I mean, is about integers.


    David
  3. p45cal

    p45cal Well-Known Member

    Messages:
    1,001
    A UDF?
    Check formula in O1 of the attached.

    Attached Files:

    Thomas Kuriakose likes this.
  4. bines53

    bines53 Active Member

    Messages:
    672
    Hi @ p45cal,

    I mean the formula, nevertheless, thank you!


    David
  5. p45cal

    p45cal Well-Known Member

    Messages:
    1,001
    Well, it is a formula! but I think I understand what you mean.
    I see in the title you want to find the series of numbers; in the attached I've added conditional formatting to show which match.

    Attached Files:

    Thomas Kuriakose likes this.
  6. bines53

    bines53 Active Member

    Messages:
    672
    Hi @ p45cal,

    UDF ,I have expanded it further, I attach the file with the UDF .

    I was curious, was there any other way to solve the issue ,

    And not with UDF.

    David

    Attached Files:

  7. bines53

    bines53 Active Member

    Messages:
    672
    Hello friends ,

    EXP, Is a mathematical constant,an irrational number,
    Millions of decimal places,in Excel is displayed, only at 15 decimal places.

    =SUM(--(MMULT(EXP(A1:D20),{1;1;1;1})=SUM(INDEX(EXP(H1:K1),0))))

    For each series of numbers, in an equal number of numbers, each series has its own identity(integers) !

    If we take this series of numbers --- 1,1,1,2

    SUM(INDEX(EXP(H1:K1),0))=15.5439015843078 ,

    This is the exclusive identity of this series !

    Can not create the same identity, with 4 other digits(integers).

    The only limit, which can be placed up to 700 in each cell, the highest, and minus 620 to low.

    I want to be careful, not to put in the same series positive and negative numbers together.

    David
    Lori likes this.
  8. Lori

    Lori Active Member

    Messages:
    125
    Hi David,

    It's a nice idea in theory based, i believe, on the principle that exp(n) is not a linear combination of any lower powers {exp(1),..,exp(n-1)} i.e. e is a transcendental number which was first shown in 1882 (https://en.wikipedia.org/wiki/Transcendental_number). For example it would not work with sqrt(2) instead of e.

    Unfortunately in practice with the double numeric type used by Excel we only have 15 digits of precision which severely limits the combinations that are unique. Try for instance your formula with combinations 36,1,1,2 and 36,2,2,1. It will say they are identical.
  9. bines53

    bines53 Active Member

    Messages:
    672
    Hi Lori,

    Thank you for your attention,

    I see now where Excel main problem is that ,he does not present at all digits

    after the point, and therefore forced him to accept it, by dividing,

    =SUM(--(MMULT(EXP(1/A1:D20),{1;1;1;1})=SUM(INDEX(EXP(1/K1:N1),0))))


    I hope it's all right now,



    David
  10. bines53

    bines53 Active Member

    Messages:
    672
    Hi Lori,


    The dividing in 1 should be so,

    =SUM(--(MMULT(1/EXP(A1:D20),{1;1;1;1})=SUM(INDEX(1/EXP(K1:N1),0))))

    It work well with zero,

    David
  11. Lori

    Lori Active Member

    Messages:
    125
    Hi David, I think the first alternative you give does better than the second for non-zero integers. eg 1, 2, 2, 37 and 1, 2, 2, 36 will be matched in the formula above.

    But thanks for the interesting idea. Anyone else interested in the theory behind the formula can follow the proof that e is transcendental from the wikipedia link. Note that being irrational is not enough. For example replacing e with √2, the combinations 1, 1, 5 and 3, 3, 3 would give the same result.

    On the other hand i don't really see a problem with using the COUNTIF formula which allows for arbitrary numbers. It can also be modified to an ISNUMBER(MATCH combination if you need array inputs.
    Last edited: Jan 11, 2018
    bines53 likes this.
  12. bines53

    bines53 Active Member

    Messages:
    672
    Hi Lori,


    =SUM(--(MMULT(--(ISNUMBER(MATCH(A1:D20,K1:N1,0))),{1;1;1;1})=4))

    The result is not accurate,

    We can summarize and say,When the data does not include the zero, the

    formula can be used ,

    =SUM(--(MMULT(EXP(1/A1:D20),{1;1;1;1})=SUM(INDEX(EXP(1/K1:N1),0))))

    Anyway, it seems to me that in any situation UDF is better than COUNTIF ,when the criteria are multiple.


    David
  13. Lori

    Lori Active Member

    Messages:
    125
    Indeed you're right, that MATCH formula only works reliably with unique criteria. But then COUNTIF is not reliable in general either eg 1, 1, 1, 1 could match criteria 1, 1, 2, 2 with that kind of logic.

    Alternatively, a formula that combines the two approaches could work well for any data type (numeric, text or boolean) provided criteria data is not too large (up to around 50 columns):

    =SUM(--(MMULT(IFERROR(N(INDEX(EXP(1/MATCH(A1:D20,H1:K1,0)),)),),{1;1;1;1})=SUM(INDEX(EXP(1/MATCH(H1:K1, H1:K1,0)),))))
  14. Lori

    Lori Active Member

    Messages:
    125
    Further testing shows all previous EXP formulas should really be adjusted to a wider tolerance, so instead of:

    =SUM(EXP({3,3,3,1}))=SUM(EXP({1,3,3,3}))

    which returns FALSE, use something like:

    =ABS(SUM(EXP({3,3,3,1})-SUM(EXP({1,3,3,3}))<1E-12
    bines53 likes this.
  15. bines53

    bines53 Active Member

    Messages:
    672
    Hi Lori,

    Do you mean,

    =SUM(--(ABS(MMULT(EXP(A1:D20),{1;1;1;1})-SUM(INDEX(EXP(H1:K1),0)))<0.000000000001))

    It looks great, if it circumvents the limitations of Excel !


    David
  16. Lori

    Lori Active Member

    Messages:
    125
    Hi David,

    That works better but I'm not 100% sure about it due to decimal approximations.

    It leads me to one final idea that only compares whole numbers...

    Replace EXP(n) with the n th prime number and multiply!

    =PRODUCT(CHOOSE(H1:K1,1,2,3,5,7))

    i.e. 1*1*1*2 and the number of matches in the array is:

    =SUM(--(EXP(MMULT(LN(CHOOSE(A1:D20,1,2,3,5,7)),{1;1;1;1})) = 2))

    This way could also be adapted using MATCH for general data and you can use CHOOSE(N(IF({1} to avoid CSE.

    Lori
  17. bines53

    bines53 Active Member

    Messages:
    672
    Hi Lori,

    There is also such a solution, but not flexible,

    =SUM(--(MMULT(--(A1:D20=1),{1;1;1;1})=3)*(MMULT(--(A1:D20=2),{1;1;1;1})=1))

    David
  18. Lori

    Lori Active Member

    Messages:
    125
    Previous suggestion was designed to work efficiently with sample data.

    For any numeric data (as well as for text), can try:

    =SUM(--(MMULT((COUNTIF(H1:K1,A1:D20)>0)*10^COUNTIF(H1:K1,"<="&A1:D20)-10^COUNTIF(H1:K1,"<="&H1:K1),{1;1;1;1})=0))
    Last edited: Jan 24, 2018
    Chihiro, r2c2 and bines53 like this.
  19. bines53

    bines53 Active Member

    Messages:
    672
    Hi Lori,

    It is great !

    Why did you wait until now?:)


    By the way ,this formula does not work

    =SUM(--(EXP(MMULT(LN(CHOOSE(A1:D20,1,2,3,5,7)),{1;1;1;1})) = 2))

    AND ,When 1,1,1,1, then the function,COUNTIF,Shoul be

    =SUM(--(MMULT(COUNTIF(H1:K1,A1:D20),{1;1;1;1})=16)),4*4.

    Thanks for your cooperation, and I hope there will be more ideas later!

    David
    Last edited: Jan 24, 2018
  20. Lori

    Lori Active Member

    Messages:
    125
    Hi David,

    Thanks, good challenge! A fully reliable solution was found eventually, based on the ideas of yours further up.

    Just to clarify what i said before...

    - in that first formula with CTRL+SHIFT+ENTER:
    replace '2' with 'PRODUCT(CHOOSE(H1:K1,1,2,3,5,7))'

    - in that second formula, try with either of these:
    H1:K1 = 1,1,2,2 -> =8 and A1:D1=1,1,1,1
    H1:K1 = 1,5,3,3 -> =6 and A11:D11=5,5,3,3

    Rgds, Lori

Share This Page