Forum Discussion

Ricky Blackwell's avatar
Ricky Blackwell
Copper Contributor
May 28, 2018

Help with a Formula please

Hi,

 

Here is how the sheet looks so far

 

Dip Sampling RejectedDip Sample CleansedDip Sample TotalSuccess RateTo Success RateTarget
14580.00% 96%
000   
000   
000   
000   
000   
000   

 

So my problem is I am trying to work out how many more cleansed items I need to hit my target of 96% in Success Rate. The cleansed and rejected items automatically increase the Dip sample total and the Success Rate amends to reflect this throughout the day. I need it to tell me based on the info populated how many more cleansed I would need to turn the current success rate into my target % in the to success rate field. My target can be raised or lowered depending on the project so need it to use whatever my target is set as in that box. I would also like it to return Target Met when the target % has been hit or gone above(this is not essential though. I have added a test sheet please anyone if you can help this would be amazing as I am pulling my hair out and need this for work asap.

Formula help
Formulas & Functions
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 28, 2018

    Maybe try using the User Defined Function (UDF) below:

     

    Function GetCleansed(Cleansed As Integer, Total As Integer, PercentTarget As Double)

    Dim x As Integer
    
    For x = 1 To 10000
        If Cleansed / Total >= PercentTarget Then
            GetCleansed = "Target Met"
            Exit Function
        ElseIf (Cleansed + x) / (Total + x) > PercentTarget Then
            GetCleansed = Cleansed + x
            Exit Function
        End If
    Next x
    GetCleansed = "Large Number...."
End Function

     

    If you're unfamiliar with UDFs then please take a look at this link for some additional information:

     

    https://www.vertex42.com/blog/excel-formulas/custom-user-defined-functions.html

  • John Jairo Vergara Domínguez's avatar
    John Jairo Vergara Domínguez
    Brass Contributor
    May 28, 2018

    Hi, to both!

     

    You could use this formula too (non CSE) in E2:

     

    =IF(B2="","",IFERROR(B2+MATCH(F2,INDEX((B2+ROW($1:$10000))/(C2+ROW($1:$10000)),)),"Target Met"))

     

    And drag it down.  Blessings!

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 31, 2018

    Hey Ricky-

     

    Just wanted to follow up and see if one of the two solutions provided were able to get the end result you were after.  If not please let me know and I'm happy to further assist.

Resources