May 11 2022 12:19 PM
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?
May 11 2022 12:53 PM
@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
May 11 2022 01:08 PM
@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?
May 11 2022 02:07 PM