Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-10-2012, 09:38 PM
sandsoppa sandsoppa is offline similar formula of averageifs() like for standard deviation Windows 7 similar formula of averageifs() like for standard deviation Office 2010 (Version 14.0)
Novice
similar formula of averageifs() like for standard deviation
 
Join Date: Oct 2010
Posts: 8
sandsoppa is on a distinguished road
Question similar formula of averageifs() like for standard deviation

I used averageifs() to conditionally select scattered cells in a column to calculate a mean given the cell meet certain criteria. I need also to calculate the standard deviation of these same selected cells. I would expect the excel may have a similar formula something like: stdevifs(). Do anyone knows anything like this? or how to get around to calculate a standard deviation with the same selected cells?


appreciate for any comments or answers.
sandy
Reply With Quote
  #2  
Old 01-12-2012, 09:23 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline similar formula of averageifs() like for standard deviation Windows 7 32bit similar formula of averageifs() like for standard deviation Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi Sandy,

Excel doesn't currently have the equivalent of a STDEVIFS() function, so you would either have to use a helper column or an array formula to get the same effect.

For example, if you have these values in F4:G9
Code:
a  1
a  3
b  4
a  5
a  6
Then, this array formula (CTRL+SHIFT+ENTER) would give you the STDEV corresponding to "a"
Code:
=STDEV(IF(F4:F8="a",G4:G8))
This formula gives the same result as
Code:
=STDEV(G4,G5,G7,G8)
Does that help?
Reply With Quote
  #3  
Old 01-12-2012, 01:08 PM
sandsoppa sandsoppa is offline similar formula of averageifs() like for standard deviation Windows 7 similar formula of averageifs() like for standard deviation Office 2010 (Version 14.0)
Novice
similar formula of averageifs() like for standard deviation
 
Join Date: Oct 2010
Posts: 8
sandsoppa is on a distinguished road
Default

great! it works and thanks.
Reply With Quote
  #4  
Old 03-28-2013, 01:59 AM
annapfeiffer annapfeiffer is offline similar formula of averageifs() like for standard deviation Windows 7 32bit similar formula of averageifs() like for standard deviation Office 2010 32bit
Novice
 
Join Date: Mar 2013
Posts: 1
annapfeiffer is on a distinguished road
Default stdevifs with date range selection

Awesome! This is so cool.

Two more questions if you or anyone can help out please:

1. multiple conditions and if I can use AND formula like this

=STDEV(IF(AND(LOGICAL1, LOGICAL2...)

2 select a date range in M200:M500 if the date is bigger than 11/1 and smaller than 12/1

Thanks so much.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
similar formula of averageifs() like for standard deviation Comment in Word similar to Excel eradem Word 3 10-01-2011 11:54 AM
How to set standard format for all TDV Word 0 07-02-2011 09:41 PM
How do you create a list similar to an itunes list? hatemail13 Excel 1 08-06-2010 02:21 AM
deviation RolandVet Excel 0 04-18-2010 12:38 AM
[Office]How to create project timeline similar to this? Coderedpl Project 0 09-11-2009 01:23 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:35 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft