Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a range of cells and I want a formula that says if all these contains the same values or not.

Usually for two cells I would use if but I want a solution that would work for ranges. I don't want to have 20 IFs one under another.

I'm comparing string values.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

If you are dealing with a range of known size then you could use CountIf to get the number of times a value appears in the range.

share|improve this answer

If the values are just numbers, you can use the STDEV function - if they are identical, STDEV will be exactly zero.

share|improve this answer

You can use the Exact Function (Text Category, but will work with numbers)

Sort each range the same way. Exact will compare cell to cell and return true if it matches exactly and false if it doesn't. Autofill makes it quick and easy to do to two ranges.

share|improve this answer
    
This doesn't answer the question as I understand it. OP has a single range and wants to know if all the cells in the range are the same. He is not comparing 2 different ranges. –  Shevek Mar 25 '10 at 9:02
    
I didn't understand it that way, but my answer is still correct. He can use the exact function to compare one cell to all the others and discover if any don't match. Or, he can copy the range and use Exact to compare them both. –  Jordan 1GT Mar 26 '10 at 2:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.