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?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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))
 
Upvote 0
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
 
Upvote 0
Thanks for the help! When I try to close it with CTRL SHIFT ENTER, nothing happens. I've tried it both ways.
 
Upvote 0
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.
 
Upvote 0
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>

.
 
Upvote 0
Finally figured it out - it has to be the CTRL and SHIFT on the right side of the keyboard. Thanks for the help!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top