Hi Deano,

Your formula averages 0 and c6:c9 correctly.
Easiest is to use one extra column:

=IF(AND(B6=$B$3,B6<$B$4),C6,"")

and average that column

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"deano" wrote in message ups.com...
|
| 11/01/06 10.0
| 11/02/06 10.0
| 11/03/06 10.0
| 11/04/06 10.0
| 11/05/06 10.0
| 11/06/06 10.0
| 11/07/06 10.0
| 11/08/06 10.0
| 11/09/06 10.0
| 11/10/06 10.0
|
| the above range is placed in B6:B15
|
| cell B3 contains 11/01/06
| cell B4 contains 11/05/06
|
| doing a simple average(C6:C9) produces 10
| placing a conditional average if cells B6:B15 are between 11/01/06 and
| 11/05/06 , average corresponding cells in C6:C15 as in expression
|
| {=AVERAGE(AND(B6:B15=B3,B6:B15<B4),C6:C15)} produces an incorrect
| result = 9.09
|
| why is that ?
|
| cheers,
| deano
|