counting

[simonkf]

I need to create a formula/macro that will scroll down a column
counting the number of instances of data. I think this is bette
explained by the example below

1
1.1
1.2
1.3

2
2.1
2.2
2.3
2.4
2.5
I need to count the number of whole numbers and also the number o
decimals under each one. Each set of numbers is variable in length,
may have 3 steps and another 100s.

If any one can help that'd be much appreciated.
Thanks
Simo

 

[schuurke28]

something like this?


Sub testje()
Dim i As Long, value As Variant, decimals As Long, wholenumbers A
Integer
For i = 1 To ActiveSheet.UsedRange.Rows.Count
value = Cells(i, 1).value
If IsNumeric(value) And Not IsEmpty(value) Then
'point or comma for numbers are depending of p
settings
If InStr(1, value, ",") + InStr(1, value, ",") >
Then
decimals = decimals + 1
Else
wholenumbers = wholenumbers + 1
End If
End If
Next i

MsgBox "# decimals : " & decimals & vbCrLf & "# wholenumbers : "
wholenumbers

End Su

 

[schuurke28]

something like this?

Sub nrValues()
Dim i As Long, value As Variant, decimals As Long, wholenumbers A
Integer
For i = 1 To ActiveSheet.UsedRange.Rows.Count
value = Cells(i, 1).value
If IsNumeric(value) And Not IsEmpty(value) Then
'point or comma for numbers are depending of p
settings
If InStr(1, value, ",") + InStr(1, value, ",") >
Then
decimals = decimals + 1
Else
wholenumbers = wholenumbers + 1
End If
End If
Next i

MsgBox "# decimals : " & decimals & vbCrLf & "# wholenumbers : "
wholenumbers

End Su

 

[Domenic]

Whole numbers...

=SUMPRODUCT(--(1-ISNUMBER(SEARCH(".",A1:A100))),--(A1:A100<>""))

Number of decimals...

=SUMPRODUCT(--(ISNUMBER(SEARCH("1.",A1:A100))))

OR

=SUMPRODUCT(--(ISNUMBER(SEARCH(B1&".",A1:A100))))

...where B1 contains the number of interest.

Hope this helps

 

[Guest]

=SUMPRODUCT((INT(A1:A11)>0)*1) ' for the interger count
=SUMPRODUCT((A1:A11-INT(A1:A11)>0)*1) 'for the fractional count

 

[schuurke28]

last post is not correct!
=SUMPRODUCT((INT(A1:A11)>0)*1) ' for the integer count

this will count all numbers as integer

 

[Myrna Larson]

Your second formula works for me, but not the first.

Putting the 4 numbers 1, 1.1, 1.2, 1.3, into A1:A4, and writing the formula

=SUMPRODUCT((INT(A1:A4)>0)*1)

gives a result of 4, not 1. You are, in effect, just counting the number of
cells containing numbers >= 1.

You can get the correct result of 1 by using your 2nd formula but change >0 to
=0.

These formulas will also work:

to count integers: =SUMPRODUCT(--(INT(A1:A11)=A1:A11))
to count non-integers: =SUMPRODUCT(--(INT(A1:A11)<>A1:A11))

 

[Guest]

I misunderstood the question, I assumed he wanted all the numbers that
contained at least 1 whole number. Thanks for the correction.

Lance