bluesky6688
New Member
- Joined
- Dec 24, 2013
- Messages
- 34
Good evening~
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:
<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!
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!