Average and Median formula with conditions

Hi

Please can someone help me with a formula.

I have these columns

I need a formula if possible to calculate a "A Final ug/L" result based on certain conditions.

Conditions is:

1. If a particular B group JAR is below 0.3 g/L or above 3.0 g/L then the corresponding A group JAR sample should be rejected in calculations for final value.

2. If one of the three corresponding A group JAR sample is rejected then the remaining two A group JAR samples are calculated as mean value (average) in the final value.

3. If two of the corresponding A group JAR sample is rejected then the remaining one A group JAR samples is just the final value.

4. If no JARs rejected then all three A group JAR samples calculated as median (middle value) value.

5. Zero values and blanks not included in calculation.

I really appreciate any help given.

Thanks

I believe I may have it sorted. Maybe there is a better way.

=IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),MEDIAN($AF3:$AH3),IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),($AF3+$AG3)/(($AF3<>0)+($AG3<>0)),IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),($AF3+$AH3)/(($AF3<>0)+($AH3<>0)),IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),($AG3+$AH3)/(($AG3<>0)+($AH3<>0)),IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),AND($AN3>=$AM$1,$AN3<=$AO$1)),$AH3,IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),AND($AM3>=$AM$1,$AM3<=$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),$AG3,IF(AND(AND($AL3>=$AM$1,$AL3<=$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),$AF3,IF(AND(OR($AL3<$AM$1,$AL3>$AO$1),OR($AM3<$AM$1,$AM3>$AO$1),OR($AN3<$AM$1,$AN3>$AO$1)),"B Jars outside value guideline","Problem"))))))))

Methodology

$AM$1 = lower value of 0.3

$AO$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

Apologies. Sample attached.

A better example attached.

Corrected some text regarding method I used.

Methodology

$I$1 = lower value of 0.3

$K$1 = upper value of 3

MEDIAN all A Jar values if all 3 B Jar values are >0.3 AND <3.0 (within range)

AVERAGE A Jar 1 and A Jar 2 metal values if B Jar 3 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 1 and A Jar 3 metal values if B Jar 2 value is <0.3 AND >3 (outside range)

AVERAGE A Jar 2 and A Jar 3 metal values if B Jar 1 value is <0.3 AND >3 (outside range)

A Jar 3 metal value if B Jar 1 and B Jar 2 value is <0.3 AND >3 (outside range)

A Jar 2 metal value if B Jar 1 and B Jar 3 value is <0.3 AND >3 (outside range)

A Jar 1 metal value if B Jar 2 and B Jar 3 value is <0.3 AND >3 (outside range)

Display "B Jars outside value guideline" if B Jar 1, B Jar 2 and B Jar 3 values is <0.3 AND >3 (outside range)

Code in the sample

=IF(AND(AND($H3>=$I$1,$H3<=$K$1),AND($I3>=$I$1,$I3<=$K$1),AND($J3>=$I$1,$J3<=$K$1)),MEDIAN($B3:$D3),IF(AND(AND($H3>=$I$1,$H3<=$K$1),AND($I3>=$I$1,$I3<=$K$1),OR($J3<$I$1,$J3>$K$1)),($B3+$C3)/(($B3<>0)+($C3<>0)),IF(AND(AND($H3>=$I$1,$H3<=$K$1),OR($I3<$I$1,$I3>$K$1),AND($J3>=$I$1,$J3<=$K$1)),($B3+$D3)/(($B3<>0)+($D3<>0)),IF(AND(OR($H3<$I$1,$H3>$K$1),AND($I3>=$I$1,$I3<=$K$1),AND($J3>=$I$1,$J3<=$K$1)),($C3+$D3)/(($C3<>0)+($D3<>0)),IF(AND(OR($H3<$I$1,$H3>$K$1),OR($I3<$I$1,$I3>$K$1),AND($J3>=$I$1,$J3<=$K$1)),$D3,IF(AND(OR($H3<$I$1,$H3>$K$1),AND($I3>=$I$1,$I3<=$K$1),OR($J3<$I$1,$J3>$K$1)),$C3,IF(AND(AND($H3>=$I$1,$H3<=$K$1),OR($I3<$I$1,$I3>$K$1),OR($J3<$I$1,$J3>$K$1)),$B3,IF(AND(OR($H3<$I$1,$H3>$K$1),OR($I3<$I$1,$I3>$K$1),OR($J3<$I$1,$J3>$K$1)),"B Jars outside value guideline","Problem"))))))))