Counting and averaging with a mix of IF and OR

[bluesky6688]

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:

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!

 

[Aladin Akyurek]

All these work perfectly! Thank you and all the guys that helped me!

Glad to hear that. Thanks for providing feedback.

 

[bluesky6688]

try something like this
edit: if you want to list them say in Column I4
array also
=INDEX($A$2:$A$10,MATCH(0,IF($D$2:$D$10<>0,COUNTIF($I$3:I3,$A$2:$A$10)),0))

Hi vlady,

I am interested in this formula but it only listed one value in the cell where the formula is. I3 is still empty (I assume the values should be listed vertically). Can you check why? I hope I could figure it out by myself but I am not there yet.

Thank you!

 

[Aladin Akyurek]

Hi vlady,

I am interested in this formula but it only listed one value in the cell where the formula is. I3 is still empty (I assume the values should be listed vertically). Can you check why? I hope I could figure it out by myself but I am not there yet.

Thank you!

F2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",
  IF(ISNUMBER($D$2:$D$10),MATCH($A$2:$A$10,$A$2:$A$10,0))),
  ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),
  ROWS(F$2:F2))),"")

Note that this test whether the D-range has a number, not any value.

 

[bluesky6688]

F2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",
  IF(ISNUMBER($D$2:$D$10),MATCH($A$2:$A$10,$A$2:$A$10,0))),
  ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),
  ROWS(F$2:F2))),"")

Note that this test whether the D-range has a number, not any value.[/QUOTE

Got it. Thank you! What I missed is copying down. I thought Excel will automatically list all the values

 

[Aladin Akyurek]

F2, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(IF($A$2:$A$10<>"",
  IF(ISNUMBER($D$2:$D$10),MATCH($A$2:$A$10,$A$2:$A$10,0))),
  ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),
  ROWS(F$2:F2))),"")

Note that this test whether the D-range has a number, not any value.

Got it. Thank you! What I missed is copying down. I thought Excel will automatically list all the values

You are welcome.