Results 1 to 8 of 8

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

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,257
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    ABCD
    10.424/1/2019 4/1/2019
    20.534/1/2019 0.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

    Spreadsheet Formulas
    CellFormula
    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. #4
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,257
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    New Member
    Join Date
    Jul 2019
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    Board Regular
    Join Date
    Oct 2011
    Posts
    4,257
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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