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((B2:D2)=$B$1,(B2:D2)<=$C$1),B3:D 3))}

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

Hi!

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

Are those real dates?

=AVERAGE(IF((B2:D2=$B$1)*(B2:D2<=$C$1),B2:D2))

Biff

"Brendan Vassallo" wrote in
message ...
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((B2:D2)=$B$1,(B2:D2)<=$C$1),B3:D 3))}

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

Try this, array-entered:
=AVERAGE(IF(((B2:D2)=$B$1)*((B2:D2)<=$C$1),B3:D3) )
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brendan Vassallo" wrote:
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((B2:D2)=$B$1,(B2:D2)<=$C$1),B3:D 3))}

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,

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

"Biff" wrote:

Hi!

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

Are those real dates?

=AVERAGE(IF((B2:D2=$B$1)*(B2:D2<=$C$1),B2:D2))

Biff

"Brendan Vassallo" wrote in
message ...
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((B2:D2)=$B$1,(B2:D2)<=$C$1),B3:D 3))}

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

You're welcome. Thanks for the feedback!

Biff

"Brendan Vassallo" wrote in
message ...
Biff,

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

"Biff" wrote:

Hi!

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

Are those real dates?

=AVERAGE(IF((B2:D2=$B$1)*(B2:D2<=$C$1),B2:D2))

Biff

"Brendan Vassallo" wrote in
message ...
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((B2:D2)=$B$1,(B2:D2)<=$C$1),B3:D 3))}

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