Archive

Archive for the ‘String Operations’ Category

Count Text in a Range

April 24th, 2009 No comments

Counting text in a range is easy. For example, if you would like count how many times “text” is in a specified range (named data), you can write something like this:
=COUNTIF(data,"*text*")

This formula will give you the count of all the cells where Excel found “text” at any place.

But let’s say you want to count wildcard characters in a range, then you can write something like this:
=SUM(IF(NOT(ISERR(FIND("*",data))),1,0))

This formula should be entered as an array formula with Ctrl + Shift + Enter. Usually, wildcard can be “escaped” using a tilde (~), but the FIND function doesn’t understand wildcard characters like the SEARCH function does.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: String Operations Tags: , , ,

Student’s t-test for equal means

October 29th, 2008 No comments

If you do not wish to enter complex formula in Excel and you already have calculated average, count, and variance for your samples(Tip:use a PivotTable), then you can use these user-defined functions to calculate the t-test stat value and degrees of freedom required to do hypothesis testing. Both the functions provide an optional argument for the assumption of equal variances; by default it is set to false.

Here’s the code for t-test:
‘—————————————————————————————
‘ Procedure : TTESTM
‘ DateTime  : 10/29/2008 08:35
‘ Author    :
‘ Purpose   : To get the t-stat value when comparing two means with an optional input
‘               for equal or unequal variances
‘               by default the function assumes that the user is comparing means with unequal variances
‘—————————————————————————————
‘
Public Function TTESTM(davg1 As Double, dcnt1 As Double, dvar1 As Double, davg2 As Double, dcnt2 As Double, dvar2 As Double, Optional blnEqVar As Boolean = False) As Double

On Error GoTo TTESTM_Error
    Dim dResult As Double, dNumer As Double, dDenon As Double, dPooledVar As Double

dNumer = davg1 – davg2

‘if equal variances are not assumed
‘http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
If Not blnEqVar Then
    dDenon = Sqr((dvar1 ^ 2 / dcnt1) + (dvar2 ^ 2 / dcnt2))
Else
‘if equal variances are assumed, then calculated the pooled variance
    dPooledVar = Sqr((((dcnt1 – 1) * dvar1 ^ 2) + ((dcnt2 – 1) * dvar2 ^ 2)) / (dcnt1 + dcnt2 – 2))
    dDenon = dPooledVar * Sqr((1 / dcnt1) + (1 / dcnt2))
End If

dResult = dNumer / dDenon

TTESTM = dResult
    Exit Function

TTESTM_Error:
    TTESTM = Null

End Function

Here’s the code for the degrees of freedom:
‘—————————————————————————————
‘ Procedure : DOFTTESTM
‘ DateTime  : 10/29/2008 08:50
‘ Author    :
‘ Purpose   : To get the degrees of freedom for the t-test when comparing two means with an optional input
‘               for equal or unequal variances
‘               by default the function assumes that the user is comparing means with unequal variances
‘—————————————————————————————
‘
Public Function DOFTTESTM(dcnt1 As Double, dvar1 As Double, dcnt2 As Double, dvar2 As Double, Optional blnEqVar As Boolean = False) As Double

    Dim dResult As Double, dNumer As Double, dDenon As Double

On Error GoTo DOFTTESTM_Error
‘if equal variances are not assumed, then use a complicated formula to compute degrees of freedom
‘http://www.itl.nist.gov/div898/handbook/eda/section3/eda353.htm
    If Not blnEqVar Then
        dNumer = ((dvar1 ^ 2 / dcnt1) + (dvar2 ^ 2 / dcnt2)) ^ 2
        dDenon = ((dvar1 ^ 2 / dcnt1) ^ 2 / (dcnt1 – 1)) + ((dvar2 ^ 2 / dcnt2) ^ 2 / (dcnt2 – 1))
        dResult = dNumer / dDenon
    Else
        dResult = dcnt1 + dcnt2 – 2
    End If
    
DOFTTESTM = dResult

    Exit Function

DOFTTESTM_Error:
    DOFTTESTM = Null

End Function

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: String Operations, Uncategorized, Useful Procedures Tags: , ,

Convert Text to Uppercase

September 17th, 2007 2 comments

If you want to convert the text to uppercase, use the following code; however, I recommend downloading ASAP Utilities, it has many functionalities, including text conversion. It doesn’t offer source code though.

Here’s my code for uppercase conversion:
(If you want to convert your text to lowercase, replace Ucase with Lcase function in the code)

‘Will convert selected range to Upper case, using array
Sub Conv2UCase()
On Error GoTo Conv2UCase_Error

Dim vDataArr As Variant
Dim lUpperBndRow As Long, lUpperBndCol As Long
Dim lRow As Long, lCol As Long

’store selected values in an array
vDataArr = Selection
‘get the upper bound of rows
lUpperBndRow = UBound(vDataArr, 1)
‘get the upper bound of cols
lUpperBndCol = UBound(vDataArr, 2)

‘Start a loop to go through all the elements of the array
For lRow = 1 To lUpperBndRow
    For lCol = 1 To lUpperBndCol
        ‘Check if the value is text, if not don’t convert
        If WorksheetFunction.IsText(vDataArr(lRow, lCol)) Then
            ‘Convert values to upper case
            vDataArr(lRow, lCol) = UCase(vDataArr(lRow, lCol))
        End If
    Next lCol
Next lRow
‘Return the converted values to the range
Selection = vDataArr
Exit Sub

Conv2UCase_Error:
    MsgBox “Error ” & Err.Number & ” (” & Err.Description & “) in Sub:Conv2UCase”
End Sub

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: String Operations, Uncategorized, Useful Procedures Tags: , ,

A function to reverse a string

August 16th, 2007 1 comment

Very simple, uses the VBA function StrReverse to reverse the input string.

‘A function to reverse a string provided as input
‘For example, the string “abcd” will become “dcba”
‘Uses the VBA function StrReverse
Public Function ReverseString(sInputString As String) As String
On Error GoTo ReverseString_Error
    
ReverseString = StrReverse(sInputString)
    Exit Function

ReverseString_Error:
    ReverseString = “#ERROR#”
End Function

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: String Operations, Useful Procedures Tags: , ,

Concatenate function

July 24th, 2007 No comments

Oh, man, I can’t tell how useful that concatenate function is.

One repetitive use I found is to create OR/AND conditions for Access queries. I copy-paste the field values of a column from Access, do some filtering and my conditions are ready. Then I use this concatfunc to create a string to use in my Access query.

For example, look at this print screen:

The Range A1:A4 houses the string condition I want to use in my Access query to restrict the fruits from my data. In cell B1 I have the formula

=PERSONAL.XLS!concatfunc(A1:A4,CHAR(34) & ” or ” & CHAR(34))

, and the return string from this function is listed in cell B1.

Now, all you have to do is copy and paste this in Access criteria and put a quotation mark at the start and at the end of this string.

I have found one more use of this when I want to store some values in an Array, using the Array function in VBA.

One more print screen:

In this example, I insert a comma (CHAR(44) instead of string OR, and this function returns a string that I can use in VBA to store these values in an array using Array function, after adding a quotation mark, of course, at the start and at the end.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: String Operations, Useful Procedures Tags: , ,