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.

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.

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.

Introducing our Online Power BI Class:

Would you like to join me on a date with Power BI? In this comprehensive online class, learn all about Power BI so you can create beautiful, insightful & interactive reports. Join me and rest of the play mates for our first ever Power BI Play Date.

 Mapping Spread of Obesity [Chart Critique and Alternatives] A quick tip about data analysis while on bike [Video + Personal]
 Written by Chandoo Tags: homework, Learn Excel, Microsoft Excel Formulas, personal 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)}

• EXCELent Bacon says:

Nice.

• daveycroc says:

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

• Jason says:

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 🙂

• GJ says:

Elegant!

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

• Leonid says:

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

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)

• Gary Ferguson says:

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

• Denys Calvin says:

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

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)

• Simon says:

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

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

• B-art in XL says:

As array formula !!

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.

 Mapping Spread of Obesity [Chart Critique and Alternatives] A quick tip about data analysis while on bike [Video + Personal]