47 responses

  1. Jason
    September 30, 2016

    =AND((COUNTA(Range)=n),(SMALL(Range,1)=1),(LARGE(Range,1)=n),(PRODUCT(Range)=FACT(n)))

    Are all the following true:
    Are there as many entries in ‘range’ as ‘n’?
    Is the smallest number 1?
    Is the largest number ‘n’?
    Is the product of all the numbers in ‘range’ the same as n factorial?

    Jason

    Reply

    • NARAYAN
      October 2, 2016

      Hi Jason ,

      That last test :

      Is the product of all the numbers in ‘range’ the same as n factorial?

      is not necessarily a valid test to check whether the range includes every number from 1 to n , and only those numbers. Always be wary of using products , since the same product can be obtained through different factors .

      For example if n = 10 , FACT(10) = 3,628,800

      This same value can be obtained by the following 2 sets of numbers :

      1,2,3,4,5,6,7,8,9,10 ——- this is a valid set in this problem

      1,2,3,2,10,6,7,8,9,10 —— this is an invalid set in this problem.

      Unfortunately , the other checks for smallest number , largest number , and number of entries in the range will all be satisfied by this invalid set.

      Reply

      • Jason
        October 3, 2016

        Aha! Thank you for pointing this out. I was trying to keep as ‘simple’ and ‘logical’ as I could, but it would appear I need a duplicate check in there too? My worry would be that some of the more advanced formulae would be too much for someone that would inherit a spread sheet containing such entries.

        Reply

  2. Chirayu
    September 30, 2016

    =IF(AND(COUNTIF(Range,1)=1,SUMIF(Range,1,Range)=1,COUNTIF(Range,2)=1,SUMIF(Range,2,Range)=2,COUNTIF(Range,3)=1,SUMIF(Range,3,Range)=3,COUNTIF(Range,4)=1,SUMIF(Range,4,Range)=4,COUNTIF(Range,5)=1,SUMIF(Range,5,Range)=5),”Yes”,”No”)

    Reply

  3. EXCELent Bacon
    September 30, 2016

    Create a range with the complete reference series in it. For this example, let’s call it RefRange.

    Array function:

    {=IF(SUM(IF(ISNUMBER(MATCH(range,RefRange,0)),1,0))=COUNT(RefRange),TRUE,FALSE)}

    Reply

  4. Micah Dail
    September 30, 2016

    Array entered.

    {=AND(COUNTIFS(range,ROW(INDIRECT(“1:”&n)))=1)}

    Reply

    • EXCELent Bacon
      September 30, 2016

      Nice.

      Reply

    • daveycroc
      September 30, 2016

      well done, was about to suggest the row(indirect to EXCELent Bacon to make their formula scalable to n

      Reply

    • Jason
      September 30, 2016

      Elegant use of INDIRECT(), a much under-used function at my desk!

      Since posting I noticed that the SMALL() and LARGE() in my answer are redundant 🙂

      Reply

    • GJ
      October 8, 2016

      Elegant!

      Reply

  5. GMF
    September 30, 2016

    If you want to know which number(s) from the range are missing, copy this CSE formula down.
    =LARGE(–ISNA(MATCH(ROW(INDIRECT(“1:”&n)),range,0))*ROW(INDIRECT(“1:”&n)),ROW(1:1))

    Reply

    • U4
      September 30, 2016

      Bravo! Thanks excel gurus. But suppose the has names and not numbers; and we want to find out the missing name/ word? Let us try to figure that out.

      Reply

      • GMF
        October 3, 2016

        RefRange has the list of names you want to check (e.g. list of invitees to a meeting) and SourceRange has the list of people who actually attended. CSE formula copied down.

        =IFERROR(INDEX(SourceRange,SMALL(IF(ISNA(MATCH(SourceRange,RefRange,0)),ROW(SourceRange)-ROW(SourceRange)+1,99^99),ROW(1:1))),””)

        Reply

      • Ufoo
        October 4, 2016

        Thanks but this formula is returning the first name in the list. Not the name which is missing. Am I making some mistake??

        Reply

      • GMF
        October 4, 2016

        Hard to tell without looking at the data. The ROW(SourceRange) should return the first row of the SourceRange, so try replacing that with a hardcoded address like ROW($A$2) or wherever your source range starts. And of course enter it as an array/CSE formula.

        Reply

      • GMF
        October 4, 2016

        Another option (a bit longer) that doesn’t require hardcoding the start of the source range is

        {=INDEX(MissingSourceRange, SMALL(IF(COUNTIF(MissingRefRange, MissingSourceRange)=0, MATCH(ROW(MissingSourceRange), ROW(MissingSourceRange)), “”),ROW(1:1)))}

        Reply

      • Ufoo
        October 5, 2016

        This formula works perfectly. Thanks a lot. And you have taught me another way of generating serial numbers; MATCH(ROW($A$2:$A$9), ROW($A$2:$A$9)

        Reply

  6. Leonid
    September 30, 2016

    ={AND(COUNTIF(Range,ROW(A1:INDEX(A:A,n,))))}

    Reply

    • Leonid
      September 30, 2016

      With handling duplicates
      =SUMPRODUCT(COUNTIF(Range,ROW(A1:INDEX(A:A,n,))))=n

      Reply

  7. RadioMo
    September 30, 2016

    Using Gauss’ sum of an arithmetic series formula, for series starting with 1:
    =IF(COUNT(Range)=n,IF((n*(n+1)/2)=SUM(Range),”Yes”,”No”),”No”)

    Reply

  8. Michael
    September 30, 2016

    =SUMPRODUCT(–(COUNTIFS(range,ROW(INDIRECT(“1:”&$I$1)))=1))=n

    Reply

    • Michael
      September 30, 2016

      Whoops forgot to change my reference to N in the previous. I originally had N in $I$1, but changed it to a named formula.

      =SUMPRODUCT(–(COUNTIFS(A$2:A$10,ROW(INDIRECT(“1:”&n)))=1))=n

      Reply

  9. Gary Ferguson
    September 30, 2016

    My forumula:
    {=IF(SUM(range,ROW(A1:INDEX(A:A,n)))=SUM(range)*2,TRUE,FALSE)}
    entered as array

    Reply

  10. Marcus Croucher
    September 30, 2016

    Similar (but more complex) than Micheal’s one above – his one is more Pareto optimal than mine!

    =SUMPRODUCT(–(COUNTIF(count,ROW(INDIRECT(“A1:A”&rangeMax)))=ROW(INDIRECT(“A1:A”&rangeMax))-ROW(INDIRECT(“A1:A”&rangeMax))+1))=rangeMax

    Reply

  11. Denys Calvin
    September 30, 2016

    =AND(COUNT(range)=n,SUM(range)=(n+1)*n/2)

    Reply

    • Gary Ferguson
      September 30, 2016

      I started out with this type formula and then realized negative or duplicate numbers can give you a false positive

      Reply

      • Denys Calvin
        September 30, 2016

        Don’t think dupes will defeat the formula. But negatives sure will! Gotta give that a think.

        Reply

  12. Narendra Babu
    October 1, 2016

    =IF(COUNT(IF(MATCH({1,2,3,4,5},A$2:A$10,0),1,0))=5,”Yes”,”No”)

    Reply

  13. SABEESH
    October 1, 2016

    =SUM(range)=MAX(range)*(MAX(range)+1)/2

    Reply

  14. GraH
    October 1, 2016

    I really need to investigate array formulas quickly to upgrade my skill!
    Fantastic replies to analyse and learn from.
    Saying thank you a lot on this blog lately, but I am just very grateful…
    So keep on going ladies and gentlemen to share your Excellent knowledge.

    Reply

  15. Irving
    October 2, 2016

    At first I thought in SUMPRODUCT, but; to avoid repeating a solution from above, something less sofisticated :)…

    =IF(IF.ERROR(VLOOKUP(1,Range,1,0),0)+IF.ERROR(VLOOKUP(2,Range,1,0)-1,0)+IF.ERROR(VLOOKUP(3,Range,1,0)-2,0)+IF.ERROR(VLOOKUP(4,Range,1,0)-3,0)+IF.ERROR(VLOOKUP(5,Range,1,0)-4,0)=5,”YES”,”NO”)

    =IF(SUM((COUNTIF(Range,1)>0)+(COUNTIF(Range,2)>0)+(COUNTIF(Range,3)>0)+(COUNTIF(Range,4)>0)+(COUNTIF(Range,5)>0))=5,”YES”,”NO”)

    Reply

  16. Jim
    October 2, 2016

    =IF(SUM(–(FREQUENCY(A2:A12,A2:A12)=1))5,”NO”,”YES”)

    Reply

  17. Jim
    October 2, 2016

    Sorry – needs to have before 5
    =IF(SUM(–(FREQUENCY(A2:A12,A2:A12)=1))5,”NO”,”YES”)

    Reply

  18. Marydas Joseph
    October 2, 2016

    =IF(AND(ISNA(MODE.MULT(A$2:A$10)),MAX(A$2:A$10)=COUNT(A$2:A$10)),”YES”,”NO”)

    Reply

  19. Simon
    October 2, 2016

    The most elegant to me is:
    {=AND(COUNTIF(range,ROW(INDIRECT(“1:”&n))))}

    This checks that every number from 1 to n appears just once in the range. If you also want to check that those are the ONLY things in the list then an additional check is needed:
    =AND(COUNTIF(range,ROW(INDIRECT(“1:”&n))), COUNTA(range)=n)

    Reply

    • Simon
      October 2, 2016

      Correction – the first array formula checks that numbers 1 to n appear AT LEAST once in the range.

      Reply

  20. Marydas Joseph
    October 3, 2016

    Hai friends…

    I suggest two formulas here.First formula self detect the maximum number and with this we can avoid link with one external cell.All the “formula range” is inside the “Range”

    Option-1
    =IF(AND(ISNA(MODE.MULT(A$3:A$11)),MAX(A$3:A$11)=COUNTIF(A$3:A$11,”>0″)),”YES”,”NO”)

    Option-2
    =IF(AND(ISNA(MODE.MULT(B$3:B$11)),COUNTIF(B$3:B$11,”>0″)=$B$1,MAX($B$3:$B$11)=$B$1),”YES”,”NO”)

    Reply

  21. Steve James
    October 3, 2016

    {=IF(SUM(COUNTIFS(range,{1;2;3;4;5}))=5,”Yes”,”No”)}

    array entered

    Reply

  22. Jason Morin
    October 3, 2016

    J1 = n
    Handles duplicates

    =IF(SUMPRODUCT(–(COUNTIF(rng,ROW(INDIRECT(“1:”&$J$1)))>=1))=$J$1,”Yes”,”No”)

    Reply

  23. B-art in XL
    October 4, 2016

    =IF(SUM(IF(COUNTIF(My_range;ROW(A1:OFFSET(A1;F2-1;0;1)))=1;1;0))=F2;”Yes”;”No”)

    Where F2 contains the value N

    Reply

    • B-art in XL
      October 4, 2016

      As array formula !!

      Reply

  24. CC
    October 4, 2016

    =AND(SUM(nrRng)=n*(n+1)/2,SUMPRODUCT(nrRng,nrRng)=SUMPRODUCT({1,2,3,…,n},{1,2,3,…,n}))

    Reply

  25. LAM
    October 5, 2016

    =SUMPRODUCT(–(1<=list)*(list<=5)) = COUNTA(list)
    while the following can be replaced: 1 for min and 5 for max, this answers TRUE or FALSE to the task at hand

    Reply

    • LAM
      October 5, 2016

      if we wish to check only the number entries:
      =SUMPRODUCT(–(1<=list)*(list<=5)*ISNUMBER(list)) = SUMPRODUCT(–ISNUMBER(list))

      Reply

  26. Vi Vi
    October 7, 2016

    =IF(AND(COUNTIF(range,1),COUNTIF(range,2),COUNTIF(range,3),COUNTIF(range,4),COUNTIF(range,5)),”Yes”,”No”)

    Reply

  27. Rahul Mangla
    October 13, 2016

    Range is a1:a10 where we enter numbers
    Enter First Number in Cell b12
    Enter Second Number in cell b13
    Enter Range: Cell B14

    rESULT USING FORMULA IN CELL B15
    Here is formula
    =IF(AND(((B13/2)*(2*B12+(B13-1)*1))=SUM(INDIRECT(B14)),PRODUCT(INDIRECT(B14))=FACT(B13),((B13/2)*(2*B12+(B13-1)*1))/B13=AVERAGE(INDIRECT(B14))),”Yes”,”No”)

    Simple maths…SUM, PRODUCT, AVERAGE of a simple number series will be unique…would never match if numbers in the series are manipulated.

    Reply

  28. Jina
    October 18, 2016

    Need help!
    How do you add column B. If B1 to B365 is equal to 1. If there is zero do not add and start again to the first row that is equal to 1. I need to know if there is straight 30, 60 and 90 days. Column A is the date field and Column B contains either 1 (without late) or 0 (with late).
    Ho many 30 days in a year? How many 60 days and how many 90 days without late in a year?

    I will appreciate any help.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top
mobile desktop