Archive

Posts Tagged ‘Formulas’

Evaluate Math functions

October 14th, 2011 admin No comments

I got a question about math functions in excel from a guy

He needed to parse a function like (3^[x1])+([x2]^2)+[x1]+[x3]+3*[x4] into a function
And needed to parse the values for the variables via a range.

I thought I would spread the wealth and share my code with the world….

voila..


 'Set reference to: Microsoft Scripting Runtime

Public Function EVAL(sFormula As String, rVarValues As Range)

    Dim tmpDict As Dictionary
    Dim Cell As Range
    Dim i As Long

    Set tmpDict = VariableDictionary(sFormula, "[", "]")

    i = 0

    For Each Cell In rVarValues

        sFormula = Replace(sFormula, tmpDict.Items(i), Cell.Value): i = i + 1

    Next tmpCell

    Set tmpDict = Nothing

    EVAL = EVALUATE(sFormula)

End Function

Private Function VariableDictionary(sString, sStart, sEnd) As Dictionary

    Dim tmpDict As Dictionary
    Dim strTemp As String
    Dim i As Long

    strTemp = ""

    Set tmpDict = New Dictionary

    On Error Resume Next

    For i = 1 To Len(sString)

        If Mid(sString, i, 1) = sStart Then strTemp = ""

        strTemp = strTemp & Mid(sString, i, 1)

        If Mid(sString, i, 1) = sEnd Then tmpDict.Add strTemp, strTemp

    Next i

    On Error GoTo 0

    Set VariableDictionary = tmpDict

    Set tmpDict = Nothing

End Function
Categories: Excel, Formulas, Math, VBA Tags: , , ,

Average and Standard Deviation without outliers in Excel

September 2nd, 2011 admin No comments

Sometimes you need the average and standard deviation of a dataset without a certain amount of outliers.

For example you want the average or standard deviation without the top 2.5% datapoints

{=AVERAGE(IF((DataRng<LARGE(DataRng,ROUNDUP((COUNT(DataRng)*0.025),0))),DataRng))}

{=STDEV.P(IF((DataRng<LARGE(DataRng,ROUNDUP((COUNT(DataRng)*0.025),0))),DataRng))}

If you want the average without the top 10 and bottom 10 datapoints this would be your solution

{=AVERAGE(IF((DataRng<LARGE(DataRng,10))*(DataRng>SMALL(DataRng,10)),DataRng))}

happy averaging or standard deviating

Categories: Excel, Formulas Tags: ,

Checking if a range is sorted with a formula

May 18th, 2011 admin No comments

January 7th, 2011

To test if range B2:B8 is sorted use {=AND(AND(B2:B7<=B3:B8)<>AND(B2:B7>=B3:B8))} in Excel
To enter a array formula ( and get the brackets ) you need to put the formula in without the brackets and press Control+Alt+Enter

thnx to Chandoo.org

Categories: Excel, Formulas Tags: ,