MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help in returning a cell reference - a bit confusing!


Posted by Chris on October 18, 2001 9:55 AM

I am building a spreadsheet and one of the formulas I use needs to change depending on the cell being referred to.

Example

START DATE: Jan 00 (entered by user)
VALUE DATE: Apr 00 (entered by user)

A B C D E
1 date N Score Average Std. Dev of Avg
2 jan 00 1 10 10 #DIV/0!
3 feb 00 2 20 15 3.5
4 mar 00 3 30 20 5.0
5 apr 00 4 40 25 6.5
6 may 00 5 50 30 7.9

A separate spreadsheet displays a summary of the statistics of the VALUE date. The sheet is something like:

DATE: Apr 00
Score: 40
AVG: 25
SD: 7.9

Now what my spreadsheet does, is using the VLOOKUP formula, finds the START date entered by user and assigns it an N. So if the user enteres Feb 00, then the spreadsheet looks like this:

START DATE: Feb 00 (entered by user)
END DATE: Apr 00 (entered by user)

A B C D E
1 date N Score Average Std. Dev of Avg
2 jan 00 0 10 10 #DIV!
3 feb 00 1 20 15 3.5
4 mar 00 2 30 20 5
5 apr 00 3 40 25 6.5
6 may 00 4 50 30 7.9

The Std. Dev calculation is dependent on the Average.
I want my average calculation to change, depending on which month is used as the start point for the calculation.

So the way the spreadsheet is set up now, the Average is calculated always using January 00 as the start of where the data begins to be used. i.e. the formula for the AVERAGE column would be =AVERAGE($C$2:C2) and then copied down for each row.
So the formula in cell D3 =AVERAGE($C$2:C3)
Similarly, the spreadsheet is set up so that the Standard Deviation is always over the entire range of averages starting from January 00. THe formula in the Standard Deviation column is =STDEV($D$2:D2), and then copied down such that the standard deviation of the averages uses the average data from January 00 as the starting point in the calculation.

The problem is, the average and thus the standard deviation should change depending on what the start date is. What I want my spreadsheet to do is calculate the averages and standard deviations from the user specified start date. So if the Start date is February 00 and the Value date is April 00:

START DATE: Feb 00
VALUE DATE: Apr 00

A B C D E
1 date N Score Average Std. Dev of Avg
2 jan 00 0 10 0 0
3 feb 00 1 20 20 #DIV/0!
4 mar 00 2 30 25 3.5
5 apr 00 3 40 30 5
6 may 00 4 50 35 6.5

And my summary spreadsheet would display:
April 00
Score: 40
Avg: 30
STD: 5

So what I need to occur is the cell my Average and STD uses as it's first start point needs to change with respect to the date that is entered by the user. So in the first case, the user entered Jan 00, so the calculations are made using Row 2 as the fixed starting point.
In the second case, Row 3 would be the fixed starting point.
I need to make it so that my formula =AVERAGE(***:D*) (where *** = starting row, and * = row calculating up until) changes so that each time a new date is entered by a user, the formula figures out which cell to begin in for its average/STD calculation and ignore all other data above that row.

Hopefully I've made myself clear!


Posted by Aladin Akyurek on October 18, 2001 12:21 PM

Chris,

Not quite sure but I believe what follows looks like what you want.

Lets say that A1:B10 has the sample data:

{"","";
"START DATE",35095;
"END DATE",35155;
"","";
"","";
35064,10;
35095,20;
35124,30;
35155,40;
35185,50}

where the big numbers are dates as they are internally represented (about which you don't have to worry -- they are just dates.

The formula below will give you the avg of values in B between user-entered dates:

In e.g., D6 enter: =AVERAGE(INDIRECT(ADDRESS(MATCH(B2,A:A,0),2)&":"&ADDRESS(MATCH(B3,A:A,0),2)))

Note that 2 refers to column B.

Standard deviation and other computations can be modeled after this formula.

Aladin

==========

Posted by Chris on October 19, 2001 5:40 AM

Aladin,

Thanks I will give this a try later on today... looks like it might help a bit.