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
    A
    Final
    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.


    AFAGAHALAMANAO
    Row 2A

    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
    A

    Final

    ug/L
    Row 343251001.360.0112.00?



    Code
    1. =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)

  • 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
    1. =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"))))))))