excell 2003 -how to use a scroll bar to adjust the range of a func

[tom]

i have several statistics functions covering a long list of data (around 1
column by 4600+ rows) i want to use a scroll bar to change the range that
the functions compute. example - AVERAGE(A1:A4600) to AVERAGE(A1:A100) i
want to be able to slide the scroll bar and shorten the range from 4600 to
about 100 or go anywhere in between to see how the average changes from the
long term to the short term.
any help would be appreciated

 

[Mike H]

Hi,

This method uses a scroll bar from the 'Control Toolbox'

Put the scroll bar into 'design mode' by click the icon on the control
toolbox toolbar right click the scrollbar click properties and then do 3
things

1. Set the min value to 100
2. set the max value to 4600
3. Enter a cell address for a linked cell (I used C1)
Exit design mode by clicking the icon

Enter this formula in a cell
=AVERAGE(INDIRECT(("A1:A"&C1)))

Adjust the scroll bar and the average will change to the range indicated by C1

Mike

 

[Mike H]

Hi,

Doing it with a scrollbar from the 'Forms' toolbox is almost the same.
Put a scrollbar on the sheet and right click it.

Set the 3 parameters by clicking the 'Control' tab

Mike