How to make a range change based on a cell value?

[baldrich]

This is my first post, so I'll probably be long winded. I'm comfortable with excel, not real comfortable with macros....but I'm willing to learn if necessary.

I am trying to perform statistical analysis of sales results for a data range that changes depending on the number of days I'm interest in analyzing. For now I'm focusing on standard deviation.

For example today I have data from 8/6/2007 back to 4/1/2007. For each day I know how many "widgets" I've sold.

If today is 8/6 and I want to calculate the standard deviation for the last 30 days, normally I would have to use the formula
=STDEV(b5:b35).

I'm trying to save a little time.

a1=today() "8/6/2007"
a2= number of days I'm interested in, it could be anything from 2 - 90. For the example lets assume 30.
b3=a1-b1 "7/7/2007 - this lets me know how far down I should highlight for my STDEV range.

Now let's say I'm only interested in the last 15 days. Instead of manually changing the standard deviation formula and retyping the range I would like to be able to change the number of days I'm interested in (a2) and have the formula recalculate based on the new range of interest.


Today's Date 8/6/2007
Days of Interest 30
Earliest day of Interest 7/7/2007

6-Aug 172
5-Aug 213
4-Aug 206
3-Aug 229
2-Aug 234
1-Aug 121
31-Jul 105
30-Jul 212
29-Jul 79
28-Jul 220
27-Jul 27
26-Jul 95
25-Jul 196
24-Jul 60
23-Jul 35
22-Jul 139
21-Jul 193
20-Jul 172
19-Jul 20
18-Jul 201
17-Jul 146
16-Jul 16
15-Jul 157
14-Jul 117
13-Jul 92
12-Jul 181
11-Jul 192
10-Jul 68
9-Jul 23
8-Jul 20
7-Jul 158
6-Jul 60
5-Jul 194
4-Jul 45
3-Jul 129
2-Jul 106
1-Jul 223
30-Jun 103
29-Jun 239
28-Jun 165
27-Jun 48
26-Jun 200
25-Jun 106
24-Jun 119
23-Jun 41
22-Jun 74
21-Jun 140
20-Jun 50
19-Jun 248
18-Jun 15
17-Jun 100
16-Jun 102
15-Jun 64
14-Jun 39
13-Jun 203
12-Jun 239
11-Jun 25
10-Jun 193
9-Jun 140
8-Jun 224
7-Jun 137
6-Jun 160
5-Jun 64
4-Jun 248
3-Jun 134
2-Jun 224
1-Jun 2
31-May 86
30-May 201
29-May 56
28-May 195
27-May 47
26-May 146
25-May 221
24-May 208
23-May 214
22-May 185
21-May 66
20-May 227
19-May 114
18-May 7
17-May 137
16-May 229
15-May 170
14-May 18
13-May 65
12-May 235
11-May 199
10-May 48
9-May 29
8-May 150
7-May 35
6-May 8
5-May 183
4-May 79
3-May 119
2-May 194
1-May 38
30-Apr 15
29-Apr 155
28-Apr 20
27-Apr 220
26-Apr 48
25-Apr 227
24-Apr 179
23-Apr 194
22-Apr 214
21-Apr 8
20-Apr 43
19-Apr 78
18-Apr 135
17-Apr 64
16-Apr 216
15-Apr 195
14-Apr 43
13-Apr 104
12-Apr 40
11-Apr 220
10-Apr 154
9-Apr 83
8-Apr 187
7-Apr 59
6-Apr 2
5-Apr 145
4-Apr 140
3-Apr 148
2-Apr 153
1-Apr 81