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.
47 Responses
=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
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.
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.
=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”)
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)}
Array entered.
{=AND(COUNTIFS(range,ROW(INDIRECT(“1:”&n)))=1)}
Nice.
well done, was about to suggest the row(indirect to EXCELent Bacon to make their formula scalable to n
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 🙂
Elegant!
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))
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.
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))),””)
Thanks but this formula is returning the first name in the list. Not the name which is missing. Am I making some mistake??
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.
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)))}
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)
={AND(COUNTIF(Range,ROW(A1:INDEX(A:A,n,))))}
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”)
=SUMPRODUCT(–(COUNTIFS(range,ROW(INDIRECT(“1:”&$I$1)))=1))=n
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
My forumula:
{=IF(SUM(range,ROW(A1:INDEX(A:A,n)))=SUM(range)*2,TRUE,FALSE)}
entered as array
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
=AND(COUNT(range)=n,SUM(range)=(n+1)*n/2)
I started out with this type formula and then realized negative or duplicate numbers can give you a false positive
Don’t think dupes will defeat the formula. But negatives sure will! Gotta give that a think.
=IF(COUNT(IF(MATCH({1,2,3,4,5},A$2:A$10,0),1,0))=5,”Yes”,”No”)
=SUM(range)=MAX(range)*(MAX(range)+1)/2
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.
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”)
=IF(SUM(–(FREQUENCY(A2:A12,A2:A12)=1))5,”NO”,”YES”)
Sorry – needs to have before 5
=IF(SUM(–(FREQUENCY(A2:A12,A2:A12)=1))5,”NO”,”YES”)
=IF(AND(ISNA(MODE.MULT(A$2:A$10)),MAX(A$2:A$10)=COUNT(A$2:A$10)),”YES”,”NO”)
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)
Correction – the first array formula checks that numbers 1 to n appear AT LEAST once in the range.
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”)
{=IF(SUM(COUNTIFS(range,{1;2;3;4;5}))=5,”Yes”,”No”)}
array entered
J1 = n
Handles duplicates
=IF(SUMPRODUCT(–(COUNTIF(rng,ROW(INDIRECT(“1:”&$J$1)))>=1))=$J$1,”Yes”,”No”)
=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
As array formula !!
=AND(SUM(nrRng)=n*(n+1)/2,SUMPRODUCT(nrRng,nrRng)=SUMPRODUCT({1,2,3,…,n},{1,2,3,…,n}))
=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
if we wish to check only the number entries:
=SUMPRODUCT(–(1<=list)*(list<=5)*ISNUMBER(list)) = SUMPRODUCT(–ISNUMBER(list))
=IF(AND(COUNTIF(range,1),COUNTIF(range,2),COUNTIF(range,3),COUNTIF(range,4),COUNTIF(range,5)),”Yes”,”No”)
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.
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.