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

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