Calculating using n number of values

[Graemea]

Hi,

I have a column of values and text, with the column header in cell A1 and in A2:A100 each cell contains either a number or “n/a”.

Can someone please suggest a formula that will allow me to calculate the STDEV.S of the last n (e.g. 15) numbers in the range. The formula needs to be able to ignore the “n/a” both in the count of the n items and in the calculation of the STDEV.S as the formula fails with non-numeric values.

Thanks!

 

[JvdV]

Just a quick thought but TAKE() should be able to return the last 15 rows:

=STDEV.S(TAKE(TOCOL(--A2:A100,3),-15))

 

[Graemea]

Thank you.

Unfortunately I don’t have access to that function on my version of Excel.

 

[HongRu]

Since the function "STDEV.S " ignores logical values and text in the sample.
I suggest you try to find all the N/As and make the cells blank.

Step1. Select Column A.
Step2. Press "Ctrl+G" then press "Alt+S" then press "F","U","X" in sequence, and then press "Enter".
Step3. Press "Del" to make all the N/A cells to be blank.
Done.
Then you can use "STDEV.S " as usual.

 

[Graemea]

Thank you both