standard deviation

[deuce]

hi, is there any way in excel to calculate a standard deviation accurately given only the start and end values without devoting cells to layout the entire range of values? For example numbers 1-5 laid out 1,2,3,4,5 would have a standard deviation of 1.5811, however what I want to do is create a formula which only requires me to input the start and end values of the range. i.e. 1 and 5 with the formula doing the rest of the calculation.

is this possible in excel?

thanks and regards.

 

[deuce]

guys, any ideas on how to accomplish this?

 

[shg]

Maybe either or these, both of which must be confirmed with Ctrl+Shift+Enter.

=STDEV(ROW(INDIRECT("1:5")))

=STDEV(ROW(INDEX(A:A, 1):INDEX(A:A, 5)))

 

[deuce]

hi, can you tell me what changes to make in the formulas when I select the cells that contain those values. In other words currently i have to select the entire range of cells containing values then apply the stdev (column) formula. The values are in decimal format like eg. x.xxxx.

 

[shg]

Those cells are no longer needed.

 

[Aladin Akyurek]

hi, can you tell me what changes to make in the formulas when I select the cells that contain those values. In other words currently i have to select the entire range of cells containing values then apply the stdev (column) formula. The values are in decimal format like eg. x.xxxx.

If you have a range reference, say A:A, and you specify a start row and an end row, you can have something like:

=STDEV(INDEX(A:A,E1):INDEX(A:A,F1))

where E1 houses a start row and F1 an end row.

If you want something like:

K2: 5
L2:12

meaning that you want calculate standard deviation reagrding the set

5,6,7,8,9,10,11,12

then, control+shift+enter, not just enter:

=STDEV(ROW(INDIRECT(K2&":"&L2)))

 

[deuce]

hi guys, sorry i dozed off while waiting for a response.

=STDEV(ROW(INDIRECT(K2&":"&L2)))

this formula works but for whole numbers only, what about if i have numbers with 5 numbers behind the decimal place?

 

[Aladin Akyurek]

hi guys, sorry i dozed off while waiting for a response.

=STDEV(ROW(INDIRECT(K2&":"&L2)))

this formula works but for whole numbers only, what about if i have numbers with 5 numbers behind the decimal place?

The ROW construct cannot generate that for you. In fact, nothing can: How many values do you think we have between just 5 and 6?

I believe you need to explain yourself why you require all that...

 

[deuce]

The numbers in the data set all have 5 figures behind the decimal place, i cannot do anything about that. eg. 1.23456.

 

[Aladin Akyurek]

The numbers in the data set all have 5 figures behind the decimal place, i cannot do anything about that. eg. 1.23456.

Deuce

It is just a math issue...

K2: 5
L2: 6

would mean

K3:

=K2*10000

L3:

=L2*10000

=SETDEV(ROW(INDIRECT(K3&":"&L3))/10000)

Such an equation would not be possibly for every arbitrary pair. BTW, you didn't yet tell why you require all this...