Standard deviation on specific days

RJDECAR

New Member
Joined
Jul 19, 2019
Messages
4
I have some data that I need averages and standard deviations on specific days. Averages are easy with Averageif but I can't find anything similar for STDEV. I've tried putting a IF statement inside STDEV but keep getting NAME or VALUE errors.

My data looks like this:
Data ProductionDate
.42 4/1
.53 4/1
.47 4/1
.62 4/1
.42 4/2
.53 4/2
.47 4/2
.62 4/1
I’ve been trying variations of the following:

=STDEV(IF(('DataPage'!R5:R2892=$A5),'DataPage'!Q5:Q2892))
Any suggestions?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
647
Office Version
365, 2013
Platform
Windows
Hi, your solution seems to be working but you simply need to turn your formula into an array (enter with Ctrl+Shift+Enter). You can also get rid of the parenthesis in the middle:
Code:
=STDEV(IF(DataPage!R5:R2892=$A5,DataPage!Q5:Q2892))
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,534
Office Version
365
Platform
Windows
Your formula is an array formula that must be entered with CTRL-SHIFT-ENTER.
See below. If you just pressed enter then do the following.
Highlight the cell.
Press F2 for edit
Press CTRL-SHIFT-ENTER.
Excel Workbook
ABCD
10.424/1/20194/1/2019
20.534/1/20190.0892749
30.474/1/2019
40.624/1/2019
50.424/2/2019
60.534/2/2019
70.474/2/2019
80.624/1/2019
Sheet
 

RJDECAR

New Member
Joined
Jul 19, 2019
Messages
4
Thanks for the help! When I try to close it with CTRL SHIFT ENTER, nothing happens. I've tried it both ways.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,534
Office Version
365
Platform
Windows
CTRL-SHIFT_ENTER works with a PC, do you have a Mac? On a Mac I think it's command-shift-return.

Note you need to hold down the keys as you enter so, CTRL+SHIHT+ENTER. Excel will put brackets around the formula if you do it right.
 

RJDECAR

New Member
Joined
Jul 19, 2019
Messages
4
I have Windows 10 with Office 365. I moved the columns so there all in the same sheet to see if that was the problem. The simplified formula is =STDEV(IF($R$5:$R$2892=T3,$Q$5:$Q$2892))
My spreadsheet looks like this

Datestdev
4/1/2019#VALUE!
Date4/2/2019#VALUE!
4/1/20194/3/2019#DIV/0!
4/1/20194/4/2019#DIV/0!
4/2/20194/5/2019#DIV/0!
4/2/20194/6/2019#DIV/0!
4/2/20194/7/2019#DIV/0!
<colgroup><col width="143" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5229;"> <col width="64" style="width: 48pt;"> <col width="72" style="width: 54pt; mso-width-source: userset; mso-width-alt: 2633;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

.
 

RJDECAR

New Member
Joined
Jul 19, 2019
Messages
4
Finally figured it out - it has to be the CTRL and SHIFT on the right side of the keyboard. Thanks for the help!
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,534
Office Version
365
Platform
Windows
You're welcome. Glad you got it to work. I have an HP laptop running Office 365 and the array formulas work using CTRL-Shift on either the left or right side of keyboard.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,029
Messages
5,484,274
Members
407,436
Latest member
Szafranski

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top