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

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:

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

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

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.

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?

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

Formula:

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.

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.

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.