Average data with date and if constraints

[Guest]

=SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W215=1),H2:H213)
This is the suggestion one of you gave me, however it doesn't seem to work.
col A are dates

I've been working on this for hours and can't get it??
Please help

 

[Don Guillett]

Did NOT test. Try this arrary formula which must be entered using
ctrl+shift+enter
=average(if(A2:A213>=C224,A2:A213<=C225,W2:W213=1,H2:H213))

 

[Guest]

when you say enter as array using cntrl, shift ,enter I don't
understand? remember I am A rookie at excel.
thanks so much for the help.

 

[Don Guillett]

when you create or edit a formula you then touch the enter key to leave the
cell. Instead hold the ctrl & shift keys down while you touch the enter key.

 

[Bob Phillips]

That is because both parts of the formula are the same, so it will always
come to 1, or it would if the ranges were the same size. You have a W2:W215
which should be W2:W213

=SUMPRODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W213=1),H2:H213)/SUMPR
ODUCT(--(A2:A213>=C224),--(A2:A213<=C225),--(W2:W213=1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

That formula will not work, you need

=AVERAGE(IF((A2:A213>=C224)*(A2:A213<=C225)*(W2:W213=1),H2:H213))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)