Formula to flag when two data ranges are simultaneously above or below various range criteria

[Ducati46]

Hi, please can you assist with a formula for column H outcome.I have tried nested if's but it gets clumsy. Column D, E and F provide the calibrations for the number of std. deviations away from the mean. If we take the values at the date 03/01/03 from sample 1 and sample 2, I need a formula that imposes a condition if both values from sample 1 and sample 2 are simultaneously > than x σ (for positive iterations from themean) or < than (for negative iterations from the mean) x standard deviations away from the mean. if the condition is met the outcome column must reflect the specific status taken from column G range. At a specific date, sample 1 may be > than +1σ and sample 2 may be > than +3σ, the formula must return >1σ (as per col. G) which will be the lower of the two σ's. Here is the spreadsheet:

A	B	C	D	E	F	G	H
date	sample 1	sample 2	std. dev's from 0	sample 1 std. dev.	sample 2 std. dev.	range	outcome
03/01/03	-0.0017	0.0086	-3.5	-0.0437	-0.0448	<(3.5) σ
06/01/03	-0.0182	-0.0051	-3	-0.0374	-0.0384	<(3)σ
13/01/03	-0.0087	0.0227	-2.5	-0.0312	-0.0320	<(2.5)σ
14/01/03	-0.0024	0.0084	-2	-0.0250	-0.0256	<(2)σ
15/01/03	0.0103	-0.0072	-1.5	-0.0187	-0.0192	<(1.5)σ
16/01/03	0.0007	-0.0079	-1	-0.0125	-0.0128	<(1)σ
17/01/03	-0.0092	0.0018	-0.5	-0.0062	-0.0064	< (0.5)σ
20/01/03	-0.0125	-0.0007	0	0.0000	0.0000	< 0σ
21/01/03	-0.0031	0.0017	0	0.0001	0.0000	>0σ
22/01/03	-0.0106	-0.0081	0.5	0.0001	0.0064	>0.5σ
23/01/03	0.0067	-0.0167	1	0.0125	0.0128	>1σ
24/01/03	-0.0026	-0.0112	1.5	0.0187	0.0192	>1.5σ
27/01/03	-0.0171	-0.0201	2	0.0250	0.0256	>2σ
28/01/03	-0.0092	-0.0034	2.5	0.0312	0.0320	>2.5σ
29/01/03	-0.0114	-0.0063	3	0.0374	0.0384	>3σ

<tbody>
</tbody>

<o></o>

 

[J.Ty.]

To be honest, I do not understand. Please tell me what values should be computed in column G for 17/01/03 and 20/01/03 and how you determine these values.

J.Ty.

 

[Ducati46]

To be honest, I do not understand. Please tell me what values should be computed in column G for 17/01/03 and 20/01/03 and how you determine these values.

J.Ty.

I am not doing a great job explaining, I will paste and if statement in the next post which will help explain the logic. Does the forum allow me to attach an excel spreadsheet?

 

[J.Ty.]

In this thread http://www.mrexcel.com/forum/excel-questions/127080-guidelines-posting-updated-22-nov-2006-a.html you have instructions concerning attaching spreadsheets.

J.Ty.