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

4 Replies

# Re: Average STD 1.5 calculation

@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

# Re: Average STD 1.5 calculation

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

# Re: Average STD 1.5 calculation

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.

Thanks.