I need to create a formula/macro that will scroll down a column,
counting the number of instances of data. I think this is better
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 of
decimals under each one. Each set of numbers is variable in length, 1
may have 3 steps and another 100s.

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


--
simonkf
------------------------------------------------------------------------
simonkf's Profile: http://www.excelforum.com/member.php...o&userid=15821
View this thread: http://www.excelforum.com/showthread...hreadid=273137

something like this?


Sub testje()
Dim i As Long, value As Variant, decimals As Long, wholenumbers As
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 pc
settings
If InStr(1, value, ",") + InStr(1, value, ",") 0
Then
decimals = decimals + 1
Else
wholenumbers = wholenumbers + 1
End If
End If
Next i

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

End Sub


--
schuurke28
------------------------------------------------------------------------
schuurke28's Profile: http://www.excelforum.com/member.php...o&userid=15818
View this thread: http://www.excelforum.com/showthread...hreadid=273137

something like this?

Sub nrValues()
Dim i As Long, value As Variant, decimals As Long, wholenumbers As
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 pc
settings
If InStr(1, value, ",") + InStr(1, value, ",") 0
Then
decimals = decimals + 1
Else
wholenumbers = wholenumbers + 1
End If
End If
Next i

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

End Sub


--
schuurke28
------------------------------------------------------------------------
schuurke28's Profile: http://www.excelforum.com/member.php...o&userid=15818
View this thread: http://www.excelforum.com/showthread...hreadid=273137

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!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273137

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



"simonkf" wrote:


I need to create a formula/macro that will scroll down a column,
counting the number of instances of data. I think this is better
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 of
decimals under each one. Each set of numbers is variable in length, 1
may have 3 steps and another 100s.

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


--
simonkf
------------------------------------------------------------------------
simonkf's Profile: http://www.excelforum.com/member.php...o&userid=15821
View this thread: http://www.excelforum.com/showthread...hreadid=273137

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

this will count all numbers as integers


--
schuurke28
------------------------------------------------------------------------
schuurke28's Profile: http://www.excelforum.com/member.php...o&userid=15818
View this thread: http://www.excelforum.com/showthread...hreadid=273137

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))


On Thu, 28 Oct 2004 07:51:05 -0700, LanceB
wrote:

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



"simonkf" wrote:


I need to create a formula/macro that will scroll down a column,
counting the number of instances of data. I think this is better
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 of
decimals under each one. Each set of numbers is variable in length, 1
may have 3 steps and another 100s.

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


--
simonkf
------------------------------------------------------------------------
simonkf's Profile:
http://www.excelforum.com/member.php...o&userid=15821
View this thread: http://www.excelforum.com/showthread...hreadid=273137

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

Lance


"Myrna Larson" wrote:

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))


On Thu, 28 Oct 2004 07:51:05 -0700, LanceB
wrote:

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



"simonkf" wrote:


I need to create a formula/macro that will scroll down a column,
counting the number of instances of data. I think this is better
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 of
decimals under each one. Each set of numbers is variable in length, 1
may have 3 steps and another 100s.

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


--
simonkf
------------------------------------------------------------------------
simonkf's Profile:
http://www.excelforum.com/member.php...o&userid=15821
View this thread: http://www.excelforum.com/showthread...hreadid=273137