Standard deviation on specific days

[RJDECAR]

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?

 

[JustynaMK]

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:

=STDEV(IF(DataPage!R5:R2892=$A5,DataPage!Q5:Q2892))

 

[AhoyNC]

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

 

[RJDECAR]

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

 

[AhoyNC]

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]

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

[TABLE="width: 257"]
<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>[TR]
[TD="width: 143, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 72, bgcolor: transparent"]Date[/TD]
[TD="width: 64, bgcolor: transparent"]stdev[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/1/2019[/TD]
[TD="bgcolor: transparent, align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Date[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/2/2019[/TD]
[TD="bgcolor: transparent, align: center"]#VALUE![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/1/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/3/2019[/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/1/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/4/2019[/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/2/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/5/2019[/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/2/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/6/2019[/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/2/2019[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]4/7/2019[/TD]
[TD="bgcolor: transparent, align: center"]#DIV/0![/TD]
[/TR]
</tbody>[/TABLE]

.

 

[RJDECAR]

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

 

[AhoyNC]

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.