# Standard deviation on specific days

#### RJDECAR

##### New Member
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?

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

 Date stdev 4/1/2019 #VALUE! Date 4/2/2019 #VALUE! 4/1/2019 4/3/2019 #DIV/0! 4/1/2019 4/4/2019 #DIV/0! 4/2/2019 4/5/2019 #DIV/0! 4/2/2019 4/6/2019 #DIV/0! 4/2/2019 4/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
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
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.

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...