Forum Discussion

MChan1330's avatar
MChan1330
Copper Contributor
May 11, 2022

Average STD 1.5 calculation

I received an Excel file and trying to figure out this formula that appears to calculate and identify an upper or lower limit from STD.

=SUM(('Data'!$S2>=

(SUM(('Data'!V$1*'Data'!$T2),'Data'!$U2)))

*(-SUM(('Data'!V$1*'Data'!$T2),'Data'!$U2,-'Data'!$S2)),

('Data'!$S2<=

(SUM('Data'!$U2,-('Data'!V$1*'Data'!$T2))))

*-(SUM('Data'!$U2,-('Data'!V$1*'Data'!$T2),-'Data'!$S2)))

 

Data below ---------- 

V1 = 1.5

S2 = 49 (RATE)

T2 = 6.46072 (STD)

U2 = 37.09 (AVG)

 

How did this resulted in 2.70892? 

excel
Formulas and Functions

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    May 11, 2022

    MChan1330 

    mtarler has already explained the parts of the formula, but (apart from the large number of unnecessary parentheses) I don't understand what you're trying to calculate. Could you explain in plain words what you want to do?

    • mtarler's avatar
      mtarler
      Silver Contributor
      May 11, 2022
      I can't speak to why but it appears they are creating a column of numbers that will show for any data point within the tolerance (V1*T2 i.e. #STDs from the Average) a 0 and for any outside that tolerance how far outside that tolerance it is.
  • mtarler's avatar
    mtarler
    Silver Contributor
    May 11, 2022

    MChan1330 First off my excel got 2.21892 (instead of 2.70892) and this is how:

    =SUM(

    ('Data'!$S2>=(SUM(('Data'!V$1*'Data'!$T2),'Data'!$U2)))*

    (-SUM(('Data'!V$1*'Data'!$T2),'Data'!$U2,-'Data'!$S2)),

    ('Data'!$S2<=(SUM('Data'!$U2,-('Data'!V$1*'Data'!$T2))))

    *-(SUM('Data'!$U2,-('Data'!V$1*'Data'!$T2),-'Data'!$S2)))

     

    so lines 2 and 4 are conditionals (>=  and <=) and the result will be TRUE or FALSE which when in a calculation a TRUE acts as a 1 and FALSE acts as a 0

    line 2 is TRUE and line 4 is FALSE

    so line 4 * line 5 is 0

    so all you need is to calculate line 3: (-SUM(('Data'!V$1*'Data'!$T2),'Data'!$U2,-'Data'!$S2))

    So V1*T2 is about 9.7 + U2 which is 37.1 - S2 which is 49 so that sum is 9.7+37.1-49 = -2.2

    but there is a "-" in front of that SUM so -2.2 becomes positive 2.2

     

    You can also use the "Evaluate Formula" in the Formula Auditing tools on the Formulas tab to help STEP through the evaluation.

    Hope that helps

Resources