 # Average and Median formula with conditions

• Hi

Please can someone help me with a formula.

I have these columns

 A ug/L JAR 1 A ug/L JAR 2 A ug/L JAR 3 B g/L JAR 1 B g/L JAR 2 B g/L JAR 3 AFinal ug/L 43 25 100 1.36 0.01 12.00 ?

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.

 AF AG AH AL AM AN AO Row 2 Aug/LJAR 1 Aug/LJAR 2 Aug/LJAR 3 Bg/LJAR 1 Bg/LJAR 2 Bg/LJAR 3 AFinalug/L Row 3 43 25 100 1.36 0.01 12.00 ?

Code
``=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.

## Files

• 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

Code
``=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"))))))))``

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!