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

**n**in your formulas.

See below examples to understand the problem.

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.

- What is the sum of values excluding items on stop list?
- Extract that 10 digit number
- Sumeria has a voter problem. Can you solve it?
- Can you transpose this address? – VBA puzzle
**Bonus:**Help! – This Excel file is infected by Sea Lions- More homework problems

=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

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

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

=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

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

Array entered.

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

Reply

Nice.

Reply

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

Reply

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

Elegant!

Reply

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

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

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

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

Reply

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

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

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

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

Reply

With handling duplicates

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

Reply

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

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

Reply

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

My forumula:

{=IF(SUM(range,ROW(A1:INDEX(A:A,n)))=SUM(range)*2,TRUE,FALSE)}

entered as array

Reply

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

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

Reply

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

Reply

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

Reply

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

Reply

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

Reply

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

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

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

Reply

Sorry – needs to have before 5

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

Reply

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

Reply

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

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

Reply

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

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

array entered

Reply

J1 = n

Handles duplicates

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

Reply

=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

As array formula !!

Reply

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

Reply

=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

if we wish to check only the number entries:

=SUMPRODUCT(–(1<=list)*(list<=5)*ISNUMBER(list)) = SUMPRODUCT(–ISNUMBER(list))

Reply

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

Reply

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

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