# Thread: Standard deviation on specific days Thanks: 0 Likes: 0

1. ## Standard deviation on specific days

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?

2. ## Re: Standard deviation on specific days

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

3. ## Re: Standard deviation on specific days

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.

 A B C D 1 0.42 4/1/2019 4/1/2019 2 0.53 4/1/2019 0.0892749 3 0.47 4/1/2019 4 0.62 4/1/2019 5 0.42 4/2/2019 6 0.53 4/2/2019 7 0.47 4/2/2019 8 0.62 4/1/2019

Spreadsheet Formulas
 Cell Formula D2 {=STDEVA(IF(\$B\$1:\$B\$8=\$D\$1,\$A\$1:\$A\$8))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

4. ## Re: Standard deviation on specific days

Thanks for the help! When I try to close it with CTRL SHIFT ENTER, nothing happens. I've tried it both ways.

5. ## Re: Standard deviation on specific days

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.

6. ## Re: Standard deviation on specific days

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

 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!

.

7. ## Re: Standard deviation on specific days

Finally figured it out - it has to be the CTRL and SHIFT on the right side of the keyboard. Thanks for the help!

8. ## Re: Standard deviation on specific days

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•