It was the list separator, how stupid of me.

The formula works perfectly, and looks brilliant.

Many thanks, guys!


"David Biddulph" wrote:

Did you try to retype Don's formula, or did you copy it from the newsgroup
and paste into your formula toolbar?
When you say "it is not being accepted", what error message did you get from
Excel?
Normally if Excel won't accept a formula it will try to put the cursor where
it thinks there may be an error. If so, where does it put the cursor?
Don has assumed that your Windows regional settings use a comma as a list
separator; do your settings use a semi-colon? If so, replace the comma in
Don's formula by a semi-colon.
Are you sure that you've got the required named ranges ChecksA and ChecksD?
[You can check with Insert/ Name/ Define...] If not, Excel will still
accept the formula, but you will get a #NAME? result from the formula.
Are you sure that the two named ranges have the same length?
--
David Biddulph

Chap wrote:
Right, but there is a problem - the formula, as I wrote it, is not
being accepted (and I did not forget the ctrl-shift-enter). I
reviewed it carefully and it seems consistent with yours. Could you
please review it, just in case you left out something?
Sorry for all the trouble.
BTW, your solution looks very elegant to me.


"Don Guillett" wrote:

checksA would have dates and checksD the values
r2 date1 and s2 date2

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chap" wrote in message
...
Sorry, I didn't quite get it.
What would be the named ranges? ChecksA and ChecksD? For the dates
and for the target values? And the Date1 and Date2 values I
mentioned, where would they come in? As R2 and S2?


"Don Guillett" wrote:

Assumes named ranges and valid dates. An array formula that must be
entered
using ctrl + shift +enter

=AVERAGE(IF((ChecksA=R2)*(ChecksA<=S2),ChecksD))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Chap" wrote in message
...
In my workbook, one worksheet (Tab1) should contain several
formulas structured as:

=AVERAGE('Tab2'!Address1:Address2)

The other worksheet (Tab2) contains a table, in which column A
are all dates
from the last 5 years, in ascending order, and column B the
corresponding
target values.

Address1 and Address2 are the cells in column B of Tab2 located
on the same
rows as the cells in column A that contain Date1 and Date2,
respectively.

Date1 and Date2 are cells (containing dates) in Tab1.

My goal is to have each formula in Tab1, given Date1 and Date2,
calculate
an
average of target values retrieved from the table in Tab2.

How should I complete the formulas? Should a Lookup function be
used?

Many thanks.
Chap