+ Reply to Thread
Results 1 to 8 of 8

Box and whisker chart, source from table

  1. #1
    Registered User
    Join Date
    01-09-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    4

    Box and whisker chart, source from table

    Hi,

    In the past Iíve made a box and whisker chart with a reference to a very simple range of numbers, and itís all worked fine.

    This time I want to extract the data from a large table full of information. Iíve got the mean sorted but I canít quite get the standard deviation and quartiles.

    Currently I have =STDEV(IF(Patients[Month]=B202,IF(Patients[Type]="Sev*",Patients[Time to Bloods]))) Ė but it returns with #Value!.

    To run you through what Iím looking for; [Month] is generated from =month(Patients[Arrival]) in the table, all working fine, B202 changes every month to the current monthís number. [Type] is a group of patients, I want all patients in the ďSevere NeutropenicĒ or just ďSevereĒ category. And finally the [Time to Bloods] is the range what I want the STDEV for. I really hope this makes sense!

    For the quartiles I have =QUARTILE(IF(Patients[Month]=B202,IF(Patients[Type]="Sev*",Patients[Time to Bloods])),1) Ė Also returns with #Value!

    If anyone could let me know what Iím doing wrong, that would be great!

    Thanks,
    Brad

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Box and whisker chart, source from table

    Hello
    Are you entering the above formulas as Arrays with Ctrl+Shift+Enter, for example on a test sheet this appears to work for me:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here I'm referencing F3 for the month number and searching the Type field for any 'Severe' occurrences. The above formula will have curly brackets {} around if entered correctly.

    DBY

  3. #3
    Registered User
    Join Date
    01-09-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    4

    Re: Box and whisker chart, source from table

    Hi DBY,

    Thanks for the help

    Forgot to mention, I am using the ctrl+shift+enter for the arrays.
    I'm still getting the #Value! problem, though I did change the arrival bit, I didnt make it clear I think.

    =STDEV(IF((MONTH(Patients[Month])=B202)*(ISNUMBER(SEARCH("Severe",Patients[Type]))),Patients[Time to Bloods]))

    I have a column in my table which goes =Month([@[Arrival Date]) - just to make it easier with working certain things out. Then I was trying to use IF Patients[Month]=B202 to give me the right date range.

    Sorry for the hassle, do you know if anythings wrong? There are some errors in the range Patients[Time to Bloods]

    Brad

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Box and whisker chart, source from table

    So the table is called 'Patients' and the 'Month' field is dates, is that correct? Also if there are errors in the 'Time to Bloods' field it will result in an error in the formula. We may need to eliminate these from the calculation.

  5. #5
    Registered User
    Join Date
    01-09-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    4

    Re: Box and whisker chart, source from table

    The table is called 'Patients' and the 'Month' column contains a number 1-12 depending on the month the patient arrived. This is probably quite messy but I'm quite new to using excel like this sorry. Is there any easy way to get rid of the errors in that?

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Box and whisker chart, source from table

    Hi
    Try the following, if the Month field is just a number 1 to 12:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Here again F3 is the month number and I've put another criteria in to hopefully eliminate the errors from Time to Bloods.

  7. #7
    Registered User
    Join Date
    01-09-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    4

    Re: Box and whisker chart, source from table

    That is absolutely brilliant thank you very much. Its working and I've manually checked the answer - it matches!

    I've copied all the IF stuff and used it for =QUARTILE and thats all working too. You're a star, its all working.

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Box and whisker chart, source from table

    That's excellent. Not to confuse you but there's also the DSTDEV database function which does a great job with varying criteria. If you're interested I could upload a small sample file.

+ 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. Can I get the source table if I click on Excel chart ?
    By pam79 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 08-15-2012, 05:50 PM
  2. How do I plot multiple data sets on a whisker chart?
    By Zahra in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 07-19-2012, 11:11 AM
  3. Box-and-whisker chart (box plots)
    By James in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-07-2011, 01:01 PM
  4. Bar chart with whisker
    By kvonm in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 11-12-2006, 11:19 PM
  5. box-and-whisker chart
    By Jan Heijs in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-10-2005, 09:06 AM

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