I found something confusing in Microsoft Excel. I fill Cell A1, A2, A3, A4 with the same number 1, then I enter the formula = A1 = A2 = A3 = A4 on cell A5, why do I get FALSE results?
Is there a way to find out the values of 4 cells are the same or different?
If all you are using is these numbers you could check for the standard deviation. If it's 0 then all values are equal:
=STDEV.P(A1:A4)
So a check for equality could look like:
=IF(STDEV.P(A1:A4),"Different","Equal)
You can use COUNTIF.
The COUNTIF returns a count of any cells that do not contain "1" which is compared to zero. If the count is zero, the formula returns TRUE. If the count is anything but zero, the formula returns FALSE.
You can apply that to any value you want to compare. Just place it before the "<>" in the formula.
This also works for "strings".
You can compare the range with the first cell, include it in the AND function and enter it as an array formula.
It will process both text and numeric values.
=AND(A1:A4=A1)
Array formula after editing is confirmed by pressing ctrl + shift + enter
