Help with a formula using INDIRECT() function

[VGPOP]

Hello again,

I was hoping if I could get help with a problem I have.

I want a formula that can calculate the average of the last 5 dates based on a specified date I give.

For example:

Let's suppose I have four worksheets, named "NORTH", "SOUTH", "EAST", "WEST".

In each worksheet, I have two columns that represent a date followed by the amount of sales I made that day in that region.

Let's assume

SOUTH (worksheet)

A1 - 11/3/07 - B1 - 387
A2 - 11/5/07 - B2 - 233
A3 - 11/7/07 - B3 - 200
A4 - 11/11/07 - B4 - 190
A5 - 11/19/07 - B5 - 100
A6 - 11/23/07 - B6 - 90
A7 - 11/25/07 - B7 - 101
A8 - 11/30/07 - B8 - 189
A9 - 12/2/07 - B9 - 177
A10 - 12/5/07 - B10 - 200
...
...
...
A17 - 12/25/07 - B17 - 167


I want to be able that by just typing "SOUTH" in a cell (B5, for example) and "11/30/07" in another cell (C5, for example) in a worksheet, I can get the average sales of the previous 5 dates before 11/30/07 (excluding this date).

So, in the above example, I will like to have the average of sales made on

11/7/07 - 200
11/11/07 - 190
11/19/07- 100
11/23/07 - 90
11/25/07 - 101

The answer is 136.2

I know that I have to use INDIRECT() function and OFFSET() to get the averages.

Any way to accomplish this?

I'd really appreciate the help.

 

[2busy2stop]

Assume D1 is where you'll put your date, D2 is where you'll put your region,


If($d$2="South",sumif(Your column a range on the the south sheet,"=>$d$1-5", your column b range on the south sheet)/5,If(D$2$= "North"...... etc etc


Youll need an if statemnet for each range i.e. N S E & W

 

[VGPOP]

Assume D1 is where you'll put your date, D2 is where you'll put your region,


If($d$2="South",sumif(Your column a range on the the south sheet,"=>$d$1-5", your column b range on the south sheet)/5,If(D$2$= "North"...... etc etc


Youll need an if statemnet for each range i.e. N S E & W

I click on the wrong link when I first made my reply. I actually hit "REPORT" link...lol.

I said that what if I want to add more worksheets?

It's going to be a really long IF() formula in that case.

 

[2busy2stop]

Can you put the data all on one sheet and include a column for the region.

If this is possible you can use pivot tables and =sumproduct or = sum(if functions to do what you want much eassier than having separate sheets.

 

[gardnertoo]

The following formula will work:

=AVERAGE(OFFSET(INDIRECT(B5&"!$A$1"),MATCH(C5,INDIRECT(B5&"!$A$1:$A$20"),1)+ISERROR(MATCH(C5,INDIRECT(B5&"!$A$1:$A$20"),0))-6,1,5,1))

The "+ISERROR..." part makes the OFFSET work even if you enter a date in cell C5 that falls between the dates on the list.

 

[VGPOP]

Can you put the data all on one sheet and include a column for the region.

If this is possible you can use pivot tables and =sumproduct or = sum(if functions to do what you want much eassier than having separate sheets.

No. To be honest. I have 30 worksheets to represent region, city, state, etc.

I want to create another worksheet and do this formula so I can stop calculating the averages.

 

[VGPOP]

The following formula will work:

=AVERAGE(OFFSET(INDIRECT(B5&"!$A$1"),MATCH(C5,INDIRECT(B5&"!$A$1:$A$20"),1)+ISERROR(MATCH(C5,INDIRECT(B5&"!$A$1:$A$20"),0))-6,1,5,1))

The "+ISERROR..." part makes the OFFSET work even if you enter a date in cell C5 that falls between the dates on the list.

It does work!

Thank you very much.

I really appreciate help!