Average STD 1.5 calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-3360271%22%20slang%3D%22en-US%22%3EAverage%20STD%201.5%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3360271%22%20slang%3D%22en-US%22%3E%3CP%3EI%20received%20an%20Excel%20file%20and%20trying%20to%20figure%20out%20this%20formula%20that%20appears%20to%20calculate%20and%20identify%20an%20upper%20or%20lower%20limit%20from%20STD.%3C%2FP%3E%3CP%3E%3DSUM(('Data'!%24S2%26gt%3B%3D%3C%2FP%3E%3CP%3E(SUM(('Data'!V%241*'Data'!%24T2)%2C'Data'!%24U2)))%3C%2FP%3E%3CP%3E*(-SUM(('Data'!V%241*'Data'!%24T2)%2C'Data'!%24U2%2C-'Data'!%24S2))%2C%3C%2FP%3E%3CP%3E('Data'!%24S2%26lt%3B%3D%3C%2FP%3E%3CP%3E(SUM('Data'!%24U2%2C-('Data'!V%241*'Data'!%24T2))))%3C%2FP%3E%3CP%3E*-(SUM('Data'!%24U2%2C-('Data'!V%241*'Data'!%24T2)%2C-'Data'!%24S2)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EData%20below%20----------%26nbsp%3B%3C%2FP%3E%3CP%3EV1%20%3D%201.5%3C%2FP%3E%3CP%3ES2%20%3D%2049%20(RATE)%3C%2FP%3E%3CP%3ET2%20%3D%206.46072%20(STD)%3C%2FP%3E%3CP%3EU2%20%3D%2037.09%20(AVG)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20did%20this%20resulted%20in%202.70892%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3360271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3360494%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20STD%201.5%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3360494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1387432%22%20target%3D%22_blank%22%3E%40MChan1330%3C%2FA%3E%26nbsp%3BFirst%20off%20my%20excel%20got%202.21892%20(instead%20of%202.70892)%20and%20this%20is%20how%3A%3C%2FP%3E%3CP%3E%3DSUM(%3C%2FP%3E%3CP%3E('Data'!%24S2%26gt%3B%3D(SUM(('Data'!V%241*'Data'!%24T2)%2C'Data'!%24U2)))*%3C%2FP%3E%3CP%3E(-SUM(('Data'!V%241*'Data'!%24T2)%2C'Data'!%24U2%2C-'Data'!%24S2))%2C%3C%2FP%3E%3CP%3E('Data'!%24S2%26lt%3B%3D(SUM('Data'!%24U2%2C-('Data'!V%241*'Data'!%24T2))))%3C%2FP%3E%3CP%3E*-(SUM('Data'!%24U2%2C-('Data'!V%241*'Data'!%24T2)%2C-'Data'!%24S2)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eso%20lines%202%20and%204%20are%20conditionals%20(%26gt%3B%3D%26nbsp%3B%20and%20%26lt%3B%3D)%20and%20the%20result%20will%20be%20TRUE%20or%20FALSE%20which%20when%20in%20a%20calculation%20a%20TRUE%20acts%20as%20a%201%20and%20FALSE%20acts%20as%20a%200%3C%2FP%3E%3CP%3Eline%202%20is%20TRUE%20and%20line%204%20is%20FALSE%3C%2FP%3E%3CP%3Eso%20line%204%20*%20line%205%20is%200%3C%2FP%3E%3CP%3Eso%20all%20you%20need%20is%20to%20calculate%20line%203%3A%26nbsp%3B(-SUM(('Data'!V%241*'Data'!%24T2)%2C'Data'!%24U2%2C-'Data'!%24S2))%3C%2FP%3E%3CP%3ESo%20V1*T2%20is%20about%209.7%20%2B%20U2%20which%20is%2037.1%20-%20S2%20which%20is%2049%20so%20that%20sum%20is%209.7%2B37.1-49%20%3D%20-2.2%3C%2FP%3E%3CP%3Ebut%20there%20is%20a%20%22-%22%20in%20front%20of%20that%20SUM%20so%20-2.2%20becomes%20positive%202.2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20use%20the%20%22Evaluate%20Formula%22%20in%20the%20Formula%20Auditing%20tools%20on%20the%20Formulas%20tab%20to%20help%20STEP%20through%20the%20evaluation.%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3360618%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20STD%201.5%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3360618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1387432%22%20target%3D%22_blank%22%3E%40MChan1330%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%20has%20already%20explained%20the%20parts%20of%20the%20formula%2C%20but%20(apart%20from%20the%20large%20number%20of%20unnecessary%20parentheses)%20I%20don't%20understand%20what%20you're%20trying%20to%20calculate.%20Could%20you%20explain%20in%20plain%20words%20what%20you%20want%20to%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3361123%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20STD%201.5%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3361123%22%20slang%3D%22en-US%22%3EI%20can't%20speak%20to%20why%20but%20it%20appears%20they%20are%20creating%20a%20column%20of%20numbers%20that%20will%20show%20for%20any%20data%20point%20within%20the%20tolerance%20(V1*T2%20i.e.%20%23STDs%20from%20the%20Average)%20a%200%20and%20for%20any%20outside%20that%20tolerance%20how%20far%20outside%20that%20tolerance%20it%20is.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3361562%22%20slang%3D%22en-US%22%3ERe%3A%20Average%20STD%201.5%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3361562%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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.