Love Chandoo.org? Here is a chance to get our most awesome courses on discount.
Checkout our 2016 Holiday SALE. Hurry up, SALE ends this Friday.

Check if a range has all numbers from 1 to n [Homework]

Posted on September 30th, 2016 in Excel Challenges , Learn Excel - 47 comments

Finally, spring weather showed up in Wellington this week. We cashed it as much as possible by going on treks, cycling trips, more treks and of course doing laundry. Here is a picture of the little ones, from today’s walk at Percy scenic reserve.

kids-in-percy-scenic-reserve

Anyways, I don’t have time to blog. I must go out and help kids with some cycling. But I want to keep you busy this weekend. So here is a fun homework problem.

Does my range have all numbers from 1 to n?

Let’s say you have a range called range (duh!).  And you want to check if range has all the numbers 1 to n (say n=5) in it, each number appearing only once (no more, no less). You can assume the named ranges range and in your formulas.

See below examples to understand the problem.

check-if-range-has-all-numbers

So go ahead and post your formulas in the comments section. I will sneak in whenever I can to look at all your creative answers.

Want more homework?

Whether you are down with bad weather or just want your fix of speadsheets for the weekend, we got you covered. Check out below puzzling problems. Be warned though, the rabbit hole is deep and mesmerizing.

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

47 Responses to “Check if a range has all numbers from 1 to n [Homework]”

  1. Jason says:

    =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

    • NARAYAN says:

      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.

      • Jason says:

        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.

  2. Chirayu says:

    =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")

  3. EXCELent Bacon says:

    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)}

  4. Micah Dail says:

    Array entered.

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

  5. GMF says:

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

    • U4 says:

      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.

      • GMF says:

        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))),"")

        • Ufoo says:

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

          • GMF says:

            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.

          • GMF says:

            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)))}

          • Ufoo says:

            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)

  6. Leonid says:

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

  7. RadioMo says:

    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")

  8. Michael says:

    =SUMPRODUCT(--(COUNTIFS(range,ROW(INDIRECT("1:"&$I$1)))=1))=n

    • Michael says:

      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

  9. Gary Ferguson says:

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

  10. Marcus Croucher says:

    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

  11. Denys Calvin says:

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

  12. Narendra Babu says:

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

  13. SABEESH says:

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

  14. GraH says:

    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.

  15. Irving says:

    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")

  16. Jim says:

    =IF(SUM(--(FREQUENCY(A2:A12,A2:A12)=1))5,"NO","YES")

  17. Jim says:

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

  18. Marydas Joseph says:

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

  19. Simon says:

    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)

  20. Marydas Joseph says:

    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")

  21. Steve James says:

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

    array entered

  22. Jason Morin says:

    J1 = n
    Handles duplicates

    =IF(SUMPRODUCT(--(COUNTIF(rng,ROW(INDIRECT("1:"&$J$1)))>=1))=$J$1,"Yes","No")

  23. B-art in XL says:

    =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

  24. CC says:

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

  25. LAM says:

    =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

    • LAM says:

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

  26. Vi Vi says:

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

  27. Rahul Mangla says:

    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.

  28. Jina says:

    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.

Leave a Reply