how to return an address from a lookup into a table?

[Chap]

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

 

[Don Guillett]

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

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

 

[Chap]

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?

 

[Ragdyer]

Why not simplify matters?

Starting date in C1 of Sheet1,
Ending date in C2 of Sheet1,
Range on Sheet2:
A2 to A100 - dates (in any order)
B2 to B100 - values

Try this formula:

=SUMPRODUCT((Sheet2!A2:A100>=C1)*(Sheet2!A2:A100<=C2)*Sheet2!B2:B100)/
SUMPRODUCT((Sheet2!A2:A100>=C1)*(Sheet2!A2:A100<=C2))

 

[Don Guillett]

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

 

[Chap]

Thanks, it worked.

Why not simplify matters?

Starting date in C1 of Sheet1,
Ending date in C2 of Sheet1,
Range on Sheet2:
A2 to A100 - dates (in any order)
B2 to B100 - values

Try this formula:

=SUMPRODUCT((Sheet2!A2:A100>=C1)*(Sheet2!A2:A100<=C2)*Sheet2!B2:B100)/
SUMPRODUCT((Sheet2!A2:A100>=C1)*(Sheet2!A2:A100<=C2))

 

[Chap]

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.

 

[Chap]

It was the list separator, how stupid of me.

The formula works perfectly, and looks brilliant.

Many thanks, guys!

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

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.