Average If

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

I want to do an annual average of quarterly data. I want to be able t
use the average like a SUMIF. I have my quaterly data in column
(31/12/2001 etc) . I have created a yearly field for each one denotin
the

Does anyone know how to craft an Average If Statement?

thank
 
Use an array function {Average(If(SubjectCol = CompareCol, Col2BAvg))} then
press [ctrl]+[shift]+[enter] will place the curly braces for you...

doco
 
Eg

ColA ColB ColC ColF ColG
month term data term Avg

1 1 100 1 =Average(If(A2:A13=F2,
C2:C13)) press control + shift + enter
2 1 200 2 =Average(If(A2:A13=F3,
C2:C13)) press control + shift + enter
3 1 300 3 =Average(If(A2:A13=F4,
C2:C13)) press control + shift + enter
4 2 400 4 =Average(If(A2:A13=F5,
C2:C13)) press control + shift + enter
5 2 500
6 2 600
7 3 700
8 3 800
9 3 900
10 4 1000
11 4 1100
12 4 1200

term being qtr here

HTH

doco
 
Hi

as average is total divided by the number of items
try
=SUMIF(range,criteria,range to sum)/COUNTIF(range,criteria)

Cheers
JulieD
 
Back
Top