Counting the numbers in a formula?

  • Thread starter Thread starter Jan GT
  • Start date Start date
J

Jan GT

Hi, I use Excel 97.

In a cell I have a formula like this:
=21+5+44-72+42+22-3+59+89+17-35+7

Now, I need to find out how many numbers
I have added or subtracted. Is it possible
to do this with a formula?

I know I could make a custom function in
vba and count the number of "+" or "-" and
add one.

If I can use an excel function, I think that
would be best

Any suggestions?


Jan
 
For most flexibility, you'll need to use VBA.

However, you can use XL4M. To display the formula in the cell to the
left, select cell B1 and choose Insert/Name/Define and enter

Name in workbook: FORMULA
Refers to: GET.CELL(6,A1)

Click Add.

If you then select F7, say, and enter =FORMULA, the formula in G7 will
be returned.

CAUTION: In XL versions prior to XL00, copying a cell with an XL4M
function to another worksheet will cause XL to crash.
 
Thank you for the answer.

Since there realy doesn't seem to be any formula that will return
the formula text in a cell (like in the formula bar) I cannot see any
way to count the numbers in the formula text. (Except with XL4M).

My solution was a custom function like this:
-------------------------------------------

Function COUNTNUMBERSINFORMULA(rng As Range) As Variant
' This function counts numbers in a formula.
' Written by Jan G. Thortensen, Kr.sand 17th.March,2004.
'
' In the cell where you want the answer, place the Formula
' like this: =COUNTNUMBERSINFORMULA(A1) Substitute "A1" with the
' cell you want to Count numbers in......................

Dim intPos As Integer
Dim i As Integer
Dim SearchString As String
Dim SearchChar As String
Dim strFormula As String
strFormula = rng.Formula
SearchString = "*/1234567890+-="
i = 1 ' if there only is one number, return 1.

For intPos = 1 To Len(strFormula)
SearchChar = Mid(strFormula, intPos, 1)

' Does the cell contain anything else but numbers...?
If InStr(1, SearchString, SearchChar) = 0 Then
COUNTNUMBERSINFORMULA = "#/NO_NUMBER###"
Exit Function
End If

' Count the number of operators...
If SearchChar = "+" Or SearchChar = "-" _
Or SearchChar = "*" Or SearchChar = "/" Then
i = i + 1
End If
Next intPos

' if no number or text is entered in cell, return 0.
If Len(strFormula) = 0 Then i = 0

COUNTNUMBERSINFORMULA = i
End Function
 
...
...
Since there realy doesn't seem to be any formula that will return
the formula text in a cell (like in the formula bar) I cannot see any
way to count the numbers in the formula text. (Except with XL4M).

My solution was a custom function like this:
Click to expand...
...

Negative numbers, e.g., =3*-2/-5, will screw up your count. Also, your function
doesn't support nonintegers, e.g., 2.7. If your udf works for you, fine, but a
more flexible alternative would be


Function cnic(r As Range) As Long
Dim i As Long, s As Long, f As String

If r.HasFormula Then
f = "+" & Mid(r.Formula, 2) & "+" 'force state change at end

If f Like "*[!0-9.*/+-]*" Then
cnic = -1
Exit Function 'return -1 error if formula contains unacceptable chars
End If

ElseIf VarType(r.Value2) = vbDouble Then
cnic = 1
Exit Function 'number constants count as one - expedient

ElseIf IsEmpty(r.Value) Then
cnic = 0
Exit Function 'blank cells count as zero

Else
cnic = -1
Exit Function 'nonnumber constants are errors, return -1 error

End If

'simple state machine
For i = 2 To Len(f)
If s = 0 And Mid(f, i, 1) = "-" Then
s = 1

ElseIf s <= 1 And Mid(f, i, 1) Like "#" Then
s = 2

ElseIf s <= 2 And Mid(f, i, 1) = "." Then
s = 3

ElseIf s <= 3 And Mid(f, i - 1, 2) Like "#[*/+-]" Then
cnic = cnic + 1
s = 0

End If

Next i

End Function
 
Thank you very much.
Wow, this is so cool. I really liked the way you solved this function.
I was never thinking in that direction with the like[0-9.*+-] -thing.

My function works in my case since I only will deal with integers and
no negative numbers (I did not need to include "-", "*" or "/" really).

However, I will use your formula for the future since it is much moore
solid. That's why NG are so great, right?

Thanks again.

Jan


Harlan Grove said:
...
..
Since there realy doesn't seem to be any formula that will return
the formula text in a cell (like in the formula bar) I cannot see any
way to count the numbers in the formula text. (Except with XL4M).

My solution was a custom function like this:
Click to expand...
..

Negative numbers, e.g., =3*-2/-5, will screw up your count. Also, your function
doesn't support nonintegers, e.g., 2.7. If your udf works for you, fine, but a
more flexible alternative would be


Function cnic(r As Range) As Long
Dim i As Long, s As Long, f As String

If r.HasFormula Then
f = "+" & Mid(r.Formula, 2) & "+" 'force state change at end

If f Like "*[!0-9.*/+-]*" Then
cnic = -1
Exit Function 'return -1 error if formula contains unacceptable chars
End If

ElseIf VarType(r.Value2) = vbDouble Then
cnic = 1
Exit Function 'number constants count as one - expedient

ElseIf IsEmpty(r.Value) Then
cnic = 0
Exit Function 'blank cells count as zero

Else
cnic = -1
Exit Function 'nonnumber constants are errors, return -1 error

End If

'simple state machine
For i = 2 To Len(f)
If s = 0 And Mid(f, i, 1) = "-" Then
s = 1

ElseIf s <= 1 And Mid(f, i, 1) Like "#" Then
s = 2

ElseIf s <= 2 And Mid(f, i, 1) = "." Then
s = 3

ElseIf s <= 3 And Mid(f, i - 1, 2) Like "#[*/+-]" Then
cnic = cnic + 1
s = 0

End If

Next i

End Function
Click to expand...
 
Back
Top