Counting a column of data (some are decimal numbers and some are integer)

Posted by Adina on February 25, 2000 7:44 AM

I have a column of data, for example:
1, 2, 2.1, 2.2, 3, 4, 5
and I need a separate count of only the decimal numbers, and a separate count of the integer numbers in this column. How do I do this?

Posted by Celia on February 25, 2000 7:25 PM

Adina
One way to do it is with the following array formulas (based on your data being in cells A1:A10).

Count of numbers containing a decimal point
=SUM(IF(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,".",""))>0,1,0))

Count of numbers not containing a decimal point
=SUM(IF(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,".",""))=0,1,0))

Celia

Posted by Adina on February 26, 2000 7:16 PM

Dear Celia,
Thank you for your response. I liked your formulas. But, I tried it and I got the error #VALUE!. I think that LEN is for text, and this is a column of numbers. Any other ideas? Thanks.

Posted by Celia on February 26, 2000 8:02 PM

Adina

The formulas are array formulas.
They will only work if you enter them by pressing Ctrl+Shift+Enter. If you just press Enter, you will get #VALUE!.

Also, please note that they are designed for cells formatted as General like the example you supplied. The formulas are based upon whether or not there is a decimal point in the number.

Celia

Posted by Celia on February 26, 2000 8:44 PM

Adina

The following array formulas should work on cells formatted either as General or as Numbers(with any number of decimal places) :-

Count of whole numbers
=SUM(IF(A1:A5-INT(A1:A5)=0,1,0))

Count of decimal numbers
=SUM(IF(A1:A5-INT(A1:A5)>0,1,0))

Celia

Posted by Adina on February 27, 2000 3:57 PM

Dear Celia
I tried all of your formulas, and they are excellent. Thanks again for all your help.