Average STD 1.5 calculation

Copper Contributor

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? 

4 Replies

@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

@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?

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 

Thanks.