if and statement with average return

[drfrankiejames]

hi - from the following data set example, i would like to return the average of column C if the criteria meets columns A & B. for example. if column A = 7 and column B = 2011, provide the average return of column C. result should be 42.113.... please help. any and all is most appreciated

best regards

8 2011 39.49073466
8 2011 40.10209175
7 2011 41.21885669
7 2011 40.36205606
7 2011 39.45128469
7 2011 41.65746244
7 2011 41.20755325
7 2011 43.41200631
7 2011 44.34023394
7 2011 44.24648197
7 2011 43.49374838
7 2011 44.51996125
7 2011 40.87890875
7 2011 43.80680931
7 2011 42.25107419
7 2011 38.73625294
6 2011 42.39128759
6 2011 42.68405703
6 2011 43.03057088
6 2011 40.92321709

[TMS]

In Excel 2003, one way:

=SUMPRODUCT((A2:A21=7)*(B2:B21=2011)*(C2:C21))/SUMPRODUCT((A2:A21=7)*(B2:B21=2011))

I'm sure there must be a neater option ... but it works.

Regards

[drfrankiejames]

thanks you very much for your help - most appreciated

[TMS]

You're welcome.

If this has answered your question, please mark your thread as solved. Please see my signature for details or the FAQ.

Regards

[drfrankiejames]

hi again - it hasnt yet unfortunately. the set of data i submitted as an example is small comparative to the actual dataset. the actual dataset grows on a daily basis, so i would like to ideally have your formula read: SUMPRODUCT((A:A=7)*(B:B=2011)*(C:C))/SUMPRODUCT((A:A=7)*(B:B=2011)), but i have either #num or #ref returned.... apologies for further work, but any suggestions?

many thanks again

[TMS]

You can't use entire column references in Excel 2003. You can, but shouldn't, in Excel 2007.

Effectively, you are are creating a matrix of 65000+ rows ... and Excel is unhappy with that.

As I said, you can do it in Excel 2007 but it's likely to be very slow.

You could, perhaps, create some dynamic named ranges for the values in columns A, B and C.

Regards