up vote 1 down vote favorite
share [g+] share [fb]

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.

link|improve this question
39% accept rate
feedback

3 Answers

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.

link|improve this answer
feedback
up vote 1 down vote

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

link|improve this answer
feedback
up vote -1 down vote

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.

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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