#### bluesky6688

##### New Member

- Joined
- Dec 24, 2013

- Messages
- 34

I am trying to analyze some experimental data I recently collected. I need some complicated formula but I am not an advanced Excel user. Any help is greatly appreciated.

So, below is a simplified data set with 3 formula I am trying to create:

1 | A(ID) | B | C | D |

2 | a | IL | L2 | 3 |

3 | a | PL | L3 | 4 |

4 | a | IL | L5 | 5 |

5 | a | IL | L2 | 6 |

6 | b | IL | L5 | |

7 | c | PL | L3 | |

8 | c | IL | L2 | |

9 | d | PL | L3 | |

10 | e | IL | L3 | 5 |

<tbody>

</tbody>

Calculation

Formula A (FA): to calculate the average all cells in D that satisfy: (B2:B10="IL")+(C2:C10="L2" or C2:C10="L3)+ISNUMBER(D2:D10).

Result: mean = 4.67 (3,6,5)

FB: Count all number of cells in D that used to calculate the average above.

N = 3 (3, 6, 5)

FC: Count all non blank cells in column D that satisfy these criteria: (B2:B10="IL") + (C2:C10="L2" or "L3") + (unique value in A2:A10).

n = 2 (a,e)

I made FA ={AVERAGE(IF(OR(C2:C10=A12,C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))}. The result is wrong 4.75, which is the average of (3,5,6,5), and is not what I expect (average of 3,6,5).

I will also need to calculate STDEV, I assume that will be similar with average.

Thanks again!