Vector function on multiple rows [solved]

goldfish

Well-known Member
Joined
Aug 23, 2005
Messages
712
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!
 

Some videos you may like

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
Joined
May 28, 2002
Messages
11,028
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
Joined
Mar 10, 2004
Messages
19,402
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!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

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! :biggrin:
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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
Joined
Mar 10, 2004
Messages
19,402

ADVERTISEMENT

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
Joined
May 28, 2002
Messages
11,028
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
Joined
Mar 10, 2004
Messages
19,402
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... :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,228
Messages
5,571,012
Members
412,353
Latest member
SofiaV
Top