# Thread: UDF to parse formula

1. ## 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. 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!

HTH

3. 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. 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. 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. You could use
Code:
`If Cell.HasFormula Then`
to check if the cell has a formula before trying to process it.

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
•