Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    UDF to parse formula

    Hi All,

    I have a column of numbers where each cell has the formula

    =nn/(rr-aa)

    This actually represents a percentage of, say, people in a class passing a test - where:
    - nn is a 1 or 2 digit number of the # of people passing
    - rr is a 1 (not likely) or 2 digit number of the # of people on the roster
    - aa is a 1 (hopefully) or 2 digit number of the # of people absent from the test (and getting a zero for the test); if zero people are absent, I still enter 0

    By subtracting the # people absent, I get a truer reading of what percentage of people pass. It's a % of people passing the test that actually took it, as opposed to the college's official view of the % of people on the roster passing the test.

    What I'd like to do is average the cells, all of which have entries per above, in the column to get a "grand average". There can be blank entries in the column that leave room for future classes.

    For example,
    A1: = 10/(20-2)
    A2: = 8/(23-4)
    A3: = 12/(19-0)

    I'd like the UDF to parse the above, add the individual components and build a new "grand average" of 30/(62-6) and return 53.57 (rounded to 2 decimal places), which I would then add a percent sign.

    I'm envisioning the UDF as being invoked with something like =grand_avg(col_range). It would loop thru the cells in the col_range, check to see if the cell is blank - if not, check the .formula property of the cell, parse the formula and add each component to a respective running total. At the end of the loop, it does the computation noted above and returns the result.

    My string manipulation capabilities, as usual, are my downfall. Invoking the function, initializing 3 running totals, looping, and returning the final calculation are pretty easy.

    Any help would be appreciated.

    If this can be done w/o a macro/UDF, that would even be better.

    TIA

    Fred

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Fred,

    Rather than creating a convoluted VBA procedure to deconstruct formulas why not just set up your spreadsheet like this.
    Fred1.JPG

    This relies on three Dynamic Range Names for the Grand Average and just standard formulas for the Pct Passed.
    You can add new rows just by typing in the values and dragging down the Pct Passed formula. The Grand Avg will automatically adjust up to a max of 2000 rows, this can be easily changed in the Name Manager on the Formulas Tab.

    No Macros Required!

    Test File: FredGradPct.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    fburg,

    If you do not want to create a table view as RG suggests or perhaps the values are outputted in the nn/(rr-aa) format, here is a UDF to what you described.

    If the cells are a string, 10/(20-2) with no equal sign, use this UDF:
    Code:
    Public Function GrandAvg(rng As Range)
    Dim nn As Integer, rr As Integer, aa As Integer, cell As Range
    nn = 0:: rr = 0:: aa = 0
    For Each cell In rng
        s = Split(cell, "/(")
        x = Replace(s(1), ")", "", 1, 1, vbTextCompare)
        t = Split(x, "-")
        nn = nn + Val(s(0))
        rr = rr + Val(t(0))
        aa = aa + Val(t(1))
    Next cell
    GrandAvg = Format(nn / (rr - aa), "0.00%")
    End Function
    Enter this formula in the averaged cell: =GrandAvg(A2:A4) for this example

    If the cells are a formula with an equal sign, =10/(20-2) then a simple average formula will work. I have created adjacent columns to see the different results. Rounding for these cells cold be applied.

    udf.png

    HTH,
    Maud

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Fred,

    The code has been modified to work if the cell contains a string (ex. "10/(20-2)" ) or has a formula (ex. =10/(20-2) ). You can see the comparison against an Average formula.

    fburg.png

    Code:
    Public Function GrandAvg(rng As Range)
    Dim nn As Integer, rr As Integer, aa As Integer, cell As Range
    nn = 0:: rr = 0:: aa = 0
    For Each cell In rng
        getFormula = cell.FormulaR1C1
        s = Split(getFormula, "/(")
        s(0) = Trim(Replace(s(0), "=", "", 1, 1, vbTextCompare))
        x = Trim(Replace(s(1), ")", "", 1, 1, vbTextCompare))
        t = Split(x, "-")
        nn = nn + Val(s(0))
        rr = rr + Val(t(0))
        aa = aa + Val(t(1))
    Next cell
    GrandAvg = Format(nn / (rr - aa), "0.00%")
    End Function

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Thanks Guys.

    I thought I posted something on Friday night - thanks to RG and Maud. And saying I couldn't redesign the spreadsheet as RG suggested. For one thing, there are numerous columns with the type of formula I mentioned.

    But I don't see it here.

    Based on Maud's first VBA post, I also had done some work on my own to use formulas rather than text strings. I thought I had succeeded with only one minor problem: the UDF would be invoked, say, in F35 but the column was only filled to row, say, F22. The remaining blank rows were to be used for future entries. Doing that gave me a #VALUE error due to the blank rows. So I changed my invocation formula to:
    =grandavg(INDIRECT("F4:F"&COUNTA(F4:F34)+3)) - the 3 is for the header rows.

    This was only after trying to fix the VBA to skip blank cells. Thought it should have been easy. I tried it 2 ways:
    - process all the blanks but do a test to see if the formula was blank (LEN < 1) - if so, goto the loop and keep checking until the end of the range - I couldn't get this to work. There would not be blank rows interspersed with filled rows, so this was really not that efficient (but we're not talking about lots of rows here).
    - on encountering the first blank (LEN < 1), exit the loop by going to the statement after the "next cell" - this did work.

    So Maud, let me take a look at your latest and compare it to what I got. I suspect, other than the error checking, they should be pretty much the same - of course, yours will be better.

    BTW: there is a difference between the 2 ways of getting the "grand average":
    - add up the components of the nn, rr, aa and redo the formula = nn/(rr-aa) for the grand avg using the totaled sums
    - take the average of the averages

    With a little bit of algebraic manipulation, one can see that the 2 alternatives above are the same only under certain conditions. In general, they'd be different.

    Fred

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You could use
    Code:
    If Cell.HasFormula Then
    to check if the cell has a formula before trying to process it.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following User Says Thank You to rory For This Useful Post:

    fburg (2015-11-09)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •