+ Reply to Thread
Results 1 to 6 of 6

STDEV with an IF not quite working (using ctl-shift-enter)

  1. #1
    Registered User
    Join Date
    08-31-2011
    Location
    San Francisco
    MS-Off Ver
    Excel 2011
    Posts
    4

    Question STDEV with an IF not quite working (using ctl-shift-enter)

    Hello-
    I think I'm close...but not quite there. I am using control-shift-enter with this function. Using Excel Mac 2011

    Trying to get the stdev for a set of data using an "if" statement where, for example, the car is red and the package number is package 1 (but I would like to be able to toggle that to a variable).

    =STDEV(IF($D$13:$D$615="Red",IF($F$13:$F$615="1",$G$13:$G$615)))

    but more generally something like
    =STDEV(IF($D$13:$D$615=A1,IF($F$13:$F$615=B1,$G$13:$G$615)))
    Where A1 is the color of the car (people can type in)
    And B1 is the package number (so people can type in 1 or 2 or 3....)

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: STDEV with an IF not quite working (using ctl-shift-enter)

    Quote Originally Posted by muchexceltolearn View Post
    ...
    =STDEV(IF($D$13:$D$615="Red",IF($F$13:$F$615="1",$G$13:$G$615)))
    ...
    And B1 is the package number (so people can type in 1 or 2 or 3....)
    Hi

    What is the problem you are having? Is the result not what you expect?

    Remark: I see that in your formula you are testing against the text value "1". Are the values in F13:F615 text values?

    It would help if you would post a small sample, for ex. rows 13:25 so that we can test.

  3. #3
    Registered User
    Join Date
    08-31-2011
    Location
    San Francisco
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: STDEV with an IF not quite working (using ctl-shift-enter)

    The calculation is returning a #N/A
    Though now I see in the range $D$13:$D$615 that there are some cells with #N/A (and likewise for $F$13:$F$615 and $G$13:$G$615).
    Is there a way to have the calc ignore all of the rows with #N/A when calculating the stdev?

    Thanks much!

  4. #4
    Valued Forum Contributor
    Join Date
    10-15-2007
    Location
    Home
    MS-Off Ver
    Office 2010, W10
    Posts
    373

    Re: STDEV with an IF not quite working (using ctl-shift-enter)

    Hi

    Can't you get rid of the error values when you generate/import the data?


    You can just filter the error values. For ex., if the error can appear in any field in any row

    =STDEV(IF(NOT(ISERROR($D$13:$D$615&$F$13:$F$615&$G$13:$G$615)),IF($D$13:$D$615="Red",IF($F$13:$F$615="1",$G$13:$G$615))))

    This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.

    You can simplify the formula if, for ex., when there is a #N/A error value all the 3 fields in the same row have it.

  5. #5
    Registered User
    Join Date
    08-31-2011
    Location
    San Francisco
    MS-Off Ver
    Excel 2011
    Posts
    4

    Re: STDEV with an IF not quite working (using ctl-shift-enter)

    Thanks lecxe. I agree those are the other options...was trying to avoid those if possible just due to how the file I have is set up. But if the only way...so it is!

  6. #6
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10
    Posts
    2,019

    Re: STDEV with an IF not quite working (using ctl-shift-enter)

    STDEVIF:
    Please Login or Register  to view this content.
    try that instead of mucking about with arrayed functions.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. array function (control+shift+enter) not working
    By SJiang in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-05-2014, 02:26 PM
  2. [SOLVED] CTRL+SHIFT+ENTER array formula not working
    By frusterated in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-05-2012, 12:02 AM
  3. Replies: 8
    Last Post: 12-12-2008, 07:12 PM
  4. SHIFT + ENTER not working on some cells Excel 2003
    By debnla in forum Excel General
    Replies: 1
    Last Post: 07-17-2007, 09:27 PM
  5. Replies: 2
    Last Post: 10-20-2005, 05:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1