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

#### baldrich

##### New Member
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

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Replies
1
Views
276
Replies
7
Views
478
Replies
0
Views
309
Replies
4
Views
433
Replies
3
Views
203

1,191,349
Messages
5,986,147
Members
440,005
Latest member
tombamber

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back