Count cells containing un-unique values in range

[BWMagee]

I have a range of values G3:DB15. In that range, some values appear just once, while others appear multiple times. I want to count the number of cells containing a value which appears more than once. (Or, count the total number of cells in range, and subtract the number of cells with values appearing just once).
So if 3 was the only value to appear more than once, and it appeared twice, the count would be 2. If 3 only appeared once, the count would be 0. If 3 appeared twice, and 4 appeared four times, the count would be 6.
Help is much appreciated!

 

[jasonb75]

No rounding has been applied (unless excel is doing its own rounding behind the scenes?)

Not rounding is more likely the case.

You could have, for example 4.21399177 and 4.2139917700000000000001 the minor difference could be due to slight variations in the original data source (you mentioned earlier that your figures were produced by formulas), or more likely it could be caused by floating point precision (google can explain that better than I can).

You may not see this as the precioson of the number displayed on screen is based on the format applied to the cell, which may not always show the exact value. In a simple example (note the result of the formula in A3)

Book1
	A
1	1
2	1
3	3
Sheet1

Cell Formulas
Range	Formula
A3	A3	=A1+A2

What I've actually done is to enter 1.4 into A1 and A2, making the result of the formula 2.8, but because I've formatted the cell to not show the decimals, the first 2 values are rounded down on screen while the formula result is rounded up. The results are correct, but what you see can be confusing.

You can step through each formula in excel to verify the results using the evaluation tool by selecting the cell with the formula, then pressing Alt m v if you do this for each of the 2 cells with 4.21399177 then you might be able to isolate the cause of the error.

 

[BWMagee]

Ok I can confirm it is some weird rounding error. Perhaps the floating point? I'm on a mac, and Alt m v didn't work. The cell formatting is set to 'general' and both instances of 4.21399177 have the same number of decimal places - 8. But when I close the formula in =ROUND(number,8) both your correct formula and the conditional formatting suddenly include it. So I will enclose everything in the round formula. Thanks very much.

 

[jasonb75]

I think General format displays up to 9 decimal places, less if your columns are too narrow to display all digits.

Background calculations are more precise, but using your example value of 4.21399177 anything in the range of 4.2139917695 to 4.21399177049999 would be truncated to 8 digits with the display rounding.

You should still have the evaluation tool on the mac, assuming that the general layout is still the same, go to the Formulas tab on the excel ribbon, there you should see an option to 'Evaluate Formula'

It is possible that with the rounding applied the other fomulas might also give the correct results, although I would have expected them all to give the same incorrect results previously