Counting and averaging with a mix of IF and OR

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:

1A(ID)BCD
2aILL23
3aPLL34
4aILL55
5aILL26
6bILL5
7cPLL3
8cILL2
9dPLL3
10eILL35

<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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
here is what I would do on the first one. See if that helps with the rest. To be honest though, this sounds suspiciously like homework.

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)

=(sumifs(D2:D10,B2:B10,"IL",C2:C10,"L2")+sumifs(D2:D10,B2:B10,"IL",C2:C10,"L3"))/(countifs(B2:B10,"IL",C2:C10,"L2")+countifs(B2:B10,"IL",C2:C10,"L3"))
 
Upvote 0
here is what I would do on the first one. See if that helps with the rest. To be honest though, this sounds suspiciously like homework.

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)

=(sumifs(D2:D10,B2:B10,"IL",C2:C10,"L2")+sumifs(D2:D10,B2:B10,"IL",C2:C10,"L3"))/(countifs(B2:B10,"IL",C2:C10,"L2")+countifs(B2:B10,"IL",C2:C10,"L3"))

Dear Dermie_72,

Thanks for your help, but this is not a homework. These are my real experimental data.
I used to import my data to SPSS then do all the stats. Now I love to use the formula to make some summary lines in a new sheet so that I can visualize the trend as I am collecting data. Calculating the average, n, STDEV, and stand error is the most part of my statistic. I can finally check the significant levels in SPSS when I want.

Regarding your formula, the sum is correct but the counting is incorrect. It doesn't exclude blank cells, but again I appreciate for your help.
 
Upvote 0
to exclude the blank cells, I would add d2:d10,"<>""" into the countifs statement to see if that works.
 
Upvote 0
I made FA ={AVERAGE(IF(OR(C2:C10=A12,C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))}.

just to correct the above

=AVERAGE(IF((C2:C10=A12)+(C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))

the plus sign is for "or" ->(C2:C10=A12) OR (C2:C10=B12)
and for "and" use asterisk (C2:C10=A12)*(C2:C10=B12)
 
Upvote 0
just to correct the above

=AVERAGE(IF((C2:C10=A12)+(C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))

the plus sign is for "or" ->(C2:C10=A12) OR (C2:C10=B12)
and for "and" use asterisk (C2:C10=A12)*(C2:C10=B12)

This works for all that I need! I can just replace the "average" with "count" or "STDEV" to get other results.

Thanks for helping!
 
Upvote 0
Now I still need help for my last formula, FC. I want to count the # of unique subjects in A2:A10, only the ones that have numbers in D2:D10.

I know the the formula to count unique values is ={SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))}, the result is 5 (a,b,c,d,e). How to add a criteria to exclude the ones that don't have any values in D2:D10, so the result will be 2 for my example data (a,e)?

Thanks a lot!
 
Upvote 0
try something like this
array also
=SUM(IF(FREQUENCY(IF(D2:D10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

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))
 
Last edited:
Upvote 0
Now I still need help for my last formula, FC. I want to count the # of unique subjects in A2:A10, only the ones that have numbers in D2:D10.

I know the the formula to count unique values is ={SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))}, the result is 5 (a,b,c,d,e). How to add a criteria to exclude the ones that don't have any values in D2:D10, so the result will be 2 for my example data (a,e)?

Thanks a lot!

1. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),1))

Even better:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

2. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",IF(D2:D10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))
 
Upvote 0
1. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),1))

Even better:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

2. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",IF(D2:D10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))

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

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top