Excel 2010 Is It Possible To 'SEARCH' Within A Formula 2

[PWD]

Good afternoon, this is probably a daft question but is it possible to 'SEARCH' within a formula in Excel 2010?

Basically we have monthly columns that I'd like to be a smarty pants and be able to use a formula to combine them, and I'm thinking that the first thing I would need to do would be to find the position of "/",

e.g. we may have Apr "=6.5/8" May "=10/12" Jun = "8/8"

Other rows could be "=89/120", so I can't tell how many characters are likely to be before the "/".

SEARCH appears to be looking at the result rather than the components of the formula.

Every ¼ this bugs me, so I thought I'd ask the question.

Many thanks,
D€$

 

[SkipVought]

Hi,

Could you upload an sample workbook? There's often more than one way to skin a cat.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[PWD]

Hi Skip, as you can see I've added all the individual components from Apr, May & Jun into the Apr-Jun column - and have left the final, bottom cell, in pink, unfinished.

Many thanks,
D€$

 

[SkipVought]

You can use this user defined function, where arg1 is the cell reference and arg2 is 0 for numerator or 1 for denominator of the expression in the referenced cell.

Function ParseFormula(rFormula As Range, iND As Integer)
    Dim a, i As Integer
    
    a = Right(rFormula.Formula, Len(rFormula.Formula) - 1)
    a = Split(a, "/")
    
    If iND >= 0 And iND <= 1 Then _
        ParseFormula = a(iND)
End Function

But in general writing formulas using literals is not a good idea. It is a maintenance nightmare. You'll have constant headaches tracking errors.

Your numerator and denominator values came from somewhere. Use the "somewhere" aggregation formulas rather than translated literal values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[PWD]

OK, thanks Skip. I'm back in work on Monday. I think I'll keep it simple. I've just found that in Excel 2013 there's "=FORMULATEXT", so I might just push for an upgrade!!

Many thanks,
D€$

 

[PWD]

Well Skip. Fortunately I'd signed up to a cat-skinning course and have managed to come to come up with a rather ingenious, if rather bonkers, solution - as demonstrated in the attached workbook. For completeness, and because I quite a nerd, I'll just run through what I did.

Copy each of the 3 'Confirmed' columns in turn and Insert them next to each original column.

In these duplicated columns, replace all = with '=.

Insert 3 more pairs of columns and use use these formulae to obtain the Numerator and Denominator respectively.
=MID(D5,2,FIND("/",D5)-2) and =RIGHT(D5,LEN(D5)-FIND("/",D5))

These can then be copied down each column and then copied across to the other 2 pairs of added columns.

It's then just a matter of adding this formula to the 'results/totals' column =(I5+K5+M5)/(J5+L5+N5) and copying it down to the end. I may see if there's any mileage in writing some code o do any of this.

Many thanks,
D€$

 

[SkipVought]

Who put the ram in the ramalamadingdong"?

Who put the NUMERATORS & DENOMINATORS in the fractions, in the cells, in the table, in the sheet, in the workbook?

What do these number represent?

Where did these numbers come from?

You already have the HOW.

Your percentages are in the form...
[tt]
=(cow)/(cow base)
[/tt]
So IMNSHO, you need a table like this...
[pre]
Upload Date Token Num Num Base
4/1/2019 Cow 65.5 83.5
4/1/2019 Dog 6.5 7
...
5/1/2019 Cow 56.5 73.5
...
[/pre]

Then your display table would be a simple SUMPRODUCT()

But AGAIN...
SOMEONE looked at SOMETHING in order to ENTER a NUMERATOR and a DEMONINATOR in a formula and hit ENTER.

WHERE did those numbers come from? I'd try to get to that source data and use IT rather than TYPING and then REVERSE ENGINEERING!!!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

If you have fixed input structures, you can use UDF, without deep error handling:

Public Function Cumulate_1div2(ParamArray vInput()) As Double
Dim vRange As Variant, rngInput As Range, Cell As Range
Dim dNumerator As Double, dDenominator As Double
Dim sFormulaText As String, vSplit As Variant
Dim sText0 As String, sText1 As String, sText2 As String

For Each vRange In vInput
    Set rngInput = vRange
    For Each Cell In rngInput
        sFormulaText = Cell.Formula
        vSplit = Split(sFormulaText, "=")
        If UBound(vSplit) = 1 Then
            sText0 = vSplit(1)
            vSplit = Split(sText0, "/")
            If UBound(vSplit) = 1 Then
                sText1 = vSplit(0): sText2 = vSplit(1)
                If IsNumeric(sText1) Then dNumerator = dNumerator + CDbl(sText1)
                If IsNumeric(sText2) Then dDenominator = dDenominator + CDbl(sText2)
            End If
        End If
    Next Cell
Next vRange
If dDenominator <> 0 Then Cumulate_1div2 = dNumerator / dDenominator
End Function

Now the formula in O5 in your recent workbook can be calculated with: [tt]=Cumulate_1div2(D5,F5,H5)[/tt]


combo

 

[PWD]

Thanks guys.

Well, "Who? (2)" I enter them.

"What?" This represents the number of patients who have been treated (for a confirmed cancer) and met a particular target over the total population treated (for a confirmed cancer).

"Where?" These come from set (on-screen) reports from our third-party cancer recording system.

So, unfortunately, there isn't an actual, useful/usable, 'source'. Additionally the on-screen report can be split between patients we see/treat - 1 point - and those we referred on to another, specialist, center for treatment - 0.5 point. It's all down to lil' ol' me to draw the figures together; and sometimes treatment may not even be confirmed for some time for any preceding month (perhaps we may be waiting for lab results to confirm if an excision showed cancer or not) so the monthly formula need revisiting each time and updating, if required, along with the the quarterly totals.

Many thanks,
D€$

 

[PWD]

@combo

I've just entered and used that Function and it works like a dream!! I've also tried it with just the first 2 months - as would happen in the real world and that's spot on too! And when I amend any of the 'original' column figures the results update appropriately!! (Obviously.)

Many thanks,
D€$

 

[combo]

Thanks to ParamArray the function accepts variable number of arguments, each argument can be single or multiple cells range.
You can adapt formula string analysis, I noticed that for "8/8", without leading "=" in the cell, its contents will be skipped (after [tt]vSplit = Split(sFormulaText, "=")[/tt] vba calculates [tt]UBound(vSplit) = 0[/tt] and skips further analysis). In this case (when [tt]UBound(vSplit) = 0[/tt]) you should continue with [tt]sText0 = sFormulaText[/tt] and split by "/".

combo

 

[SkipVought]

Here's the bottom line. You have been collecting some very valuable data, that as of now, is buried in formulas in your monthly summary sheet.

In order to make your valuable data usable, you need to have the data elements in a table something like this where the elements are clearly visible, accessible, maintainable for ANY analysis and reporting using Excel analysis and reporting features...
[pre]
Tgt Name Target Month Num Base
Target1 0.85 4/1/2019 65.5 83.5
Target1 0.85 5/1/2019 56.5 73.5
Target1 0.85 6/1/2019 41.5 63
Target2 0.9 4/1/2019 6.5 7
Target2 0.9 5/1/2019 9.5 10.5
Target2 0.9 6/1/2019 6.5 7.5
...
[/pre]

I added a Tgt Name like Target1, Target2... You likely have more descriptive names.
I took your summary and NORMALIZED it using faq68-5287.
Then I did the summary table using formulas referencing this data table...
[pre]
Tgt Name 4/1/2019 5/1/2019 6/1/2019 2Q19
Target1 78.44% 76.87% 65.87% 74.32%
Target2 92.86% 90.48% 86.67% 90.00%
Target3 100.00% 98.31% 100.00% 99.40%
Target4 100.00% 100.00% 100.00% 100.00%
Target5 100.00% 94.74% 100.00% 97.67%
Target6 #N/A #N/A #N/A #N/A
Target7 94.34% 93.10% 93.82% 93.76%
Target8 87.84% 90.61% 90.79% 89.81%
[/pre]

There are only 2 formulas in the summary table...
[tt]
month Formula: =IFERROR(SUMPRODUCT((tDATA[Tgt Name]=$A2)*(tDATA[Month]=B$1)*(tDATA[Num]))/SUMPRODUCT((tDATA[Tgt Name]=$A2)*(tDATA[Month]=B$1)*(tDATA[Base])),NA())
quarter formula: =IFERROR(SUMPRODUCT((tDATA[Tgt Name]=$A2)*(tDATA[Month]>=DATE(20&RIGHT($E$1,2),(LEFT(E$1,1)-1)*3+1,1))*(tDATA[Month]<=DATE(20&RIGHT($E$1,2),(LEFT(E$1,1)-1)*3+3,1))*(tDATA[Num]))/SUMPRODUCT((tDATA[Tgt Name]=$A2)*(tDATA[Month]>=DATE(20&RIGHT($E$1,2),(LEFT(E$1,1)-1)*3+1,1))*(tDATA[Month]<=DATE(20&RIGHT($E$1,2),(LEFT(E$1,1)-1)*3+3,1))*(tDATA[Base])),NA())
[/tt]

To change the reporting range, you need only change the reporting headings properly!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein