average if, multiple conditions... possible?

[Jennifre]

First of all thank you in advance. I have learned so much from the answers I've gotten from this board, thank you to all who've responded to my dorky queries here!

Yet, this one's got me. I have used sumproduct with multiple criteria, and how to use AverageIfs, but I'm hoping to combine that by using an averageif, with multiple criteria. What I have tried, doesn't work.

If anyone can help, I feel very grateful -- thank you for your syntax help!! My unworking effort so far:

{=AVERAGE(IF((ap$2:ap$1364=3,4),$cf$2:$cf$1364=1,2))}

 

[Robert Mika]

Maybe like this:
=IF(AVERAGE(AP$2:AP$1364)=3,4,IF(AVERAGEA($CF$2:$CF$1364)=1,2,""))

but is probably better to post your data and tell waht you going to achive(I see that you are trying to use CSE formula)

 

[repairman615]

To average a range based upon multiple conditions, the AVERAGEIFS() would do.

Where is your data and what are the conditions? It is hard to decipher the formula posted.

 

[Jennifre]

Great idea, thanks! And thank you for your query demanding I clarify what I'm trying to do!

Okay, by that code I posted earlier, I'm hoping to get a percentage, of the AP values 3 or 4 which also have CF values 1 or 2.

I could tell that my use of averageif was completely dumb as soon as I tried your option, I'm so sorry I'm suck a dork. Thank you for your help!

 

[repairman615]

Not really an average then. If I understand correctly you would like a count of the cells in column AP from row 2 to 1364 that contain a 3 or 4.

and divide that be the count of cells in CP2:CP1364 containing 1 or 2.

How about:
<TABLE style="WIDTH: 560pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=746><COLGROUP><COL style="WIDTH: 560pt; mso-width-source: userset; mso-width-alt: 13641" width=746><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d7e4bc; WIDTH: 560pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2775535 class=xl63 height=20 width=746>=(COUNTIF(AP2:AP1364,"=3")+COUNTIF(AP2:AP1364,"=4"))/(COUNTIF(CF2:CF1364,"=1")+COUNTIF(CF2:CF1364,"=2"))</TD></TR></TBODY></TABLE>

Format the cell you place this into as a percentage.
(Ctrl+Shift+%)

Does that work?

-Jeff

 

[Jennifre]

Hi Jeff, thanks. I'm not entirely sure that will give me accurate data for this.

But what I think I need is to find:

- CF values 1 or 2,
- whose rows also feature AP values 3 or 4,
- divided by the count of values 1,2,3, or 4 in AP (I think/hope...!)

To restate (hopefully correctly), I need the percentage of AP values 3 or 4 whose rows feature CF values 1 or 2.

I'm having trouble sorting out my question/need here obviously, but I think that's it.

Thank you SO much for your help!

 

[Jennifre]

Jeff... don't worry, I think I can use what you've given me for syntax. Hold please....

 

[T. Valko]

Hi Jeff, thanks. I'm not entirely sure that will give me accurate data for this.

But what I think I need is to find:

- CF values 1 or 2,
- whose rows also feature AP values 3 or 4,
- divided by the count of values 1,2,3, or 4 in AP (I think/hope...!)

To restate (hopefully correctly), I need the percentage of AP values 3 or 4 whose rows feature CF values 1 or 2.

I'm having trouble sorting out my question/need here obviously, but I think that's it.

Thank you SO much for your help!

How about posting some sample data and let us know what result you expect.

In CF, are there any zeros, empty cells or negative numbers?

 

[Jennifre]

Hi Biff, thanks for writing in.

I don't know how to post data here? (I'll give a sample below, kind of representing a snippet of the data)

And, AP contains values 1-4. No zeros or empty cells (I thought it did til I checked just now).

Thanks so much for your help.

Sample CF + AP data:

AP
1
2
4
3
1
etc

CF
2
5
7
3
1
etc

 

[T. Valko]

But what I think I need is to find:

- CF values 1 or 2,
- whose rows also feature AP values 3 or 4,
- divided by the count of values 1,2,3, or 4 in AP (I think/hope...!)

Hi Biff, thanks for writing in.

I don't know how to post data here? (I'll give a sample below, kind of representing a snippet of the data)

And, AP contains values 1-4. No zeros or empty cells (I thought it did til I checked just now).

Thanks so much for your help.

Sample CF + AP data:

AP
1
2
4
3
1
etc

CF
2
5
7
3
1
etc

See if this does what you want:

=COUNTIFS(AP2:AP10,"<=2",CF2:CF10,">=3",CF2:CF10,"<=4")/COUNTIF(AP2:AP10,"<=4")