# Vector function on multiple rows [solved]

#### goldfish

##### Well-known Member
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!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### tusharm

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

#### Domenic

##### MrExcel MVP
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

##### Well-known Member
Thanks a lot Domenic, I didn't realize how useful offset is!

#### tusharm

##### MrExcel MVP

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

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

#### tusharm

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

Domenic said:
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

##### MrExcel MVP

tusharm said:
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

##### MrExcel MVP
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)))

Domenic said:
tusharm said:
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

##### MrExcel MVP
tusharm said:
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...

Replies
4
Views
112
Replies
2
Views
255
Replies
1
Views
114
Replies
8
Views
78
Replies
2
Views
163