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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
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"))
 

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34
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.
 

dermie_72

Well-known Member
Joined
Sep 4, 2012
Messages
1,540
to exclude the blank cells, I would add d2:d10,"<>""" into the countifs statement to see if that works.
 

vlady

Active Member
Joined
Jan 26, 2012
Messages
413

ADVERTISEMENT

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)
 

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34
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!
 

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34

ADVERTISEMENT

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!
 

vlady

Active Member
Joined
Jan 26, 2012
Messages
413
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:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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))
 

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

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
Top