AVERAGE with conditions

[Attila Fust]

I am trying to average a range that meets certain
conditions. Here is the example:

worksheet1
A B
-----------------
jan 1 dec 1 (NAME=FISCAL)

worksheet2

A B C
-----------------------
jan 1 feb 1 mar 1 (NAME=DATES)
52 57 72 (NAME=R1)

I want to get the averages of the values in R1 where DATES
is within the range of FISCAL.

If I simply average the three numbers I get 60.33. Using
the following formula I get the wrong answer:

=AVERAGE((DATES>=FISCAL)*(DATES<=FISCAL)*(R1))

With this formula I get 52. I have tried different
variations but can't seem to get the answer I need.

Any help would be much appreciated.

Regards,

Attila Fust

 

[CLR]

Maybe try a mixture of formulas to get your AVERAGE.........

SUMIF / COUNTIF using your ranges and conditions

Vaya con Dios,
Chuck, CABGx3

 

[Domenic]

Hi Atilla,

Try,

=AVERAGE(IF((Dates>=Sheet1!A1)*(Dates<=Sheet1!B1),RI))

enter using CTRL+SHIFT+ENTER

Hope this helps!