Creating a conditional average formula

**G188ONS** · 12-05-2009, 11:24 AM

Hi,

In Excel 2007 I need some help off the community to create a formula for my sheet.

Basically, for my dissertation I've conducted an online questionnaire and have my data stored in an excel spreadsheet. One question asks which campus at my university they are based at (A,B,C and OTHER) whilst the second asks them to rate an aspect on a scale of 1-10.

I want to create a formula to calculate the average score people give on the scale who are from campus A, then the same for campus B and so on.

I already have the total number of people at campus a on another sheet (Q2) in the workbook which I can use to divide it to give the average as shown below

What I'm asking is something like:

=SUM(IF(C2:C1000="A", THEN(I2:I1000=?)/Q2!B3

If you can help it would be much appreciated?

**zbor** · 12-05-2009, 11:56 AM

Something like:

=AVERAGEIF(C:C,"A",I:I)

Or this:

=SUMIF(C:C,"A",I:I)/Q2!B3

**G188ONS** · 12-05-2009, 12:15 PM

This works great thanks so much. However, with my questionnaire under the other column I have allowed them the option that they can type in their campus if its none of the above. So is there a way to use
=AVERAGEIF(C:C,"A",I:I) to use anythin remaining that is not A,B or C?

Sort of like:

=AVERAGEIFNOT(C:C,"A,B,C",I:I)

**zbor** · 12-06-2009, 04:27 AM

You can try this: =AVERAGEIF(H1:H9,"<>"&"A",I1:I9)

for NOT A.

Or you can use some cell (e.g. C1) and you can write there some simbol you like so you can use it in formula as a reference:

=AVERAGEIF(H1:H9,"<>"&C1,I1:I9)

**DonkeyOte** · 12-06-2009, 04:31 AM

Originally Posted by G188ONS

This works great thanks so much. However, with my questionnaire under the other column I have allowed them the option that they can type in their campus if its none of the above. So is there a way to use
=AVERAGEIF(C:C,"A",I:I) to use anythin remaining that is not A,B or C?

Without knowing what else could be entered into Column C if not A/B/C (unspecified) you could switch from AVERAGEIF to AVERAGEIFS

=AVERAGEIFS(I:I,C:C,"<>A",C:C,"<>B",C:C,"<>C")

Note: though AVERAGEIF/S are more efficient than Arrays they are still not super efficient so if you can minimise the range references do so (ie from entire column to say rows 1 to 1000 etc...)