Vector function on multiple rows [solved]

[goldfish]

Is there a way to use a vector function to take the average of every row's standard deviation? i.e.

=AVERAGE(STDEV(1:1),....,STDEV(15:15))

The following does NOT work (understandably)

{=AVERAGE(STDEV(1:15)}

because the STDEV() function takes the whole double array, instead of simply each row, so this ends up being simply the STDEV() function.

Thanks!

 

[tusharm]

Just simplify the issue and put the 15 standard deviations in 15 cells and then calculate the average of those 15 cells.

 

[Domenic]

Try...

=AVERAGE(SUBTOTAL(7,OFFSET(A1:D15,ROW(A1:D15)-MIN(ROW(A1:D15)),0,1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly.

Hope this helps!

 

[goldfish]

Thanks a lot Domenic, I didn't realize how useful offset is!

 

[tusharm]

Yes, OFFSET is a very powerful function but unless you modified Domenic's suggestion, it doesn't meet the requirements of the original post.

Thanks a lot Domenic, I didn't realize how useful offset is!

 

[tusharm]

Using SUBTOTAL to get around the behavior of STDEV is rather clever -- sneaky but clever! I wonder how robust the technique is...

Try...

=AVERAGE(SUBTOTAL(7,OFFSET(A1:D15,ROW(A1:D15)-MIN(ROW(A1:D15)),0,1)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly.

Hope this helps!

 

[Domenic]

Yes, OFFSET is a very powerful function but unless you modified Domenic's suggestion, it doesn't meet the requirements of the original post.

Maybe I'm missing something, but I don't quite understand. Can you please explain?

 

[tusharm]

It ignores all data beyond column 15.

Use the array formula

=AVERAGE(SUBTOTAL(7,OFFSET(1:1,ROW(A1:A15)-MIN(ROW(A1:A15)),0)))

Yes, OFFSET is a very powerful function but unless you modified Domenic's suggestion, it doesn't meet the requirements of the original post.

Maybe I'm missing something, but I don't quite understand. Can you please explain?

 

[Domenic]

It ignores all data beyond column 15.

I take it you meant, "...beyond Column D". Yes, I only included Columns A through D, instead of the whole row. I used that range as an example, but I did say to adjust the range accordingly...