Array Formula

[Brendan Vassallo]

Hi All,

I am trying to write an array formula that calculates the average of an
array for a range of dates.

I have the following spreadsheet set up:

A B C D
1 Range Sep-06 Oct-06
2 Date Sep-06 Oct-06 Nov-06
3 Amount $500,000.00 $550,000.00 $600,000.00

I am using the following formula:

{=AVERAGE(IF(AND((B22)>=$B$1,(B22)<=$C$1),B33))}

The problem is that the formula is returning $0.00. I have tried many
variations of this but with no luck.

Can anyone help me write an array formula that will calculate the average of
the amount for the given range....i.e. for the range specified above the
formula should return $$525,000.

I hope I am making sense....please help.

Brendan

 

[Biff]

Hi!

Range Sep-06 Oct-06
Date Sep-06 Oct-06 Nov-06

Are those real dates?

=AVERAGE(IF((B22>=$B$1)*(B22<=$C$1),B22))

Biff

 

[Max]

Try this, array-entered:
=AVERAGE(IF(((B22)>=$B$1)*((B22)<=$C$1),B33))

 

[Brendan Vassallo]

Biff,

Yes they are real dates....the formula beow works so thanks for that....

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff,

Yes they are real dates....the formula beow works so thanks for that....