Best way to display data showing a range of values in Excel 2010..

[labessade]

Hi all, I'm quite new to Excel charts... can kind of figure out basics but I'm really struggling with this one and would really appreciate some help please? I need to plot data in a chart to paste into a Word report which I know how to do. But I can't get excel to chart my data how I want to see it and I'm not sure how I should enter it to be honest. Basically I need to show a data range for normal, impaired glucose intolerance (IGT) and diabetes based on two time frames (see values below). I tried to do a bar stack so it showed green for normal, orange in middle for IGT then Diabetes in red on top, with two separate columns - one for 0 time (before test) and the other as 120 (2hrs after). I finally got it to show what I wanted... or so I thought. The cut-off points for each against the axis on the chart were not at the levels I expected . Part of the problem is I think I don't really understand the different chart types fully and which sort I should be using. Finally, I have another set of readings from an individual (a fictitious patient) and the last part of my problem is to then plot this so you can see looking at the chart what category the person falls into. It might involve more than one chart which is ok if it has to be so... I just don't know how to achieve this when there are ranges involved. So far I've plotted my patients readings in a line chart and if I can't figure this out I might just have to display the rest of this data below it in a table. Sorry for the long winded post and hope it makes sense!!

For someone without diabetes (normal), the amount of glucose in their blood should be:
● less than 6 mmol/l before the test
● less than 7.8 mmol/l two hours after the test

If you have IGT, the amount of glucose in your blood will be:
● 6-7 mmol/l before the test
● 7.9-11 mmol/l two hours after the test

If you have diabetes, the amount of glucose in your blood will be:
● more than 7 mmol/l before the test
● more than 11 mmol/l two hours after the test

[etaf]

can you mock up the graph you would like to see with a drawing
also
Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[labessade]

Hi, Wayne
Thanks so much for looking at this for me! I've added an excel workbook for you as requested, it has two sheets; a CaseStudy sheet and a Ranges sheet. Hope it attaches correctly...

The case study sheet shows the data for the patient, correctly plotted as a stand-alone chart. The Ranges sheet shows the data for the diagnostic ranges for normal, IGT and diabetes. As you can see some have an upper and lower limit band, some are less than or more than amounts. I've tried to do a couple of graphs to give you an idea of the type of style I've thought of but neither work adequately at the moment, the line chart doesn't include upper and lower ranges and the stacked bar chart I was trying to show in bands but the values don't line up on the graph as I was hoping! I only tried it this way as didn't know how else to show ranges and I thought I could plot the patients data as a line through the chart maybe so it would show which category the 0 and 120 readings of the patient lie i.e if they are diabetic or not etc.

Sorry its a bit hard to explain... I'm not entirely sure to be honest how to display the data so these are the best mock ups I can come up with at the mo. I'll try to do a manual diagram if I can think of anything else to add, I might need to do separate charts maybe for each time point (0 and 120)? I did see someone who had done floating columns to display ranges of lower and upper readings which I thought looked cool, but I don't know how they did it, and then I'm not sure how I'd add the patient data to that. Any help/advice you can offer will be greatly received... thanks .

[labessade]

P.S. Can you let me know when you have it as I'd like to remove attachment after if that's poss... thanks!

[etaf]

can you put a sample ,that you can leave on the forum for other members to also see

[labessade]

That's ok, the data is not sensitive it's just if anyone on my course sees it they can use for their assignment lol ;-).

[etaf]

Is this an assignment ?

[labessade]

it is part of one yes... we need to display results in the report from the data I've had to analyse.

[labessade]

I take it you are not going to offer any advice on how I can do this then???? We don't get taught Excel so I'm not sure if it can be done how I want it. I will just include my one graph already completed with a table below for the categorised standard readings.... guess I need a crash course on Excel graphs at some point or I don't see how I am supposed to learn when they do not seem very intuitive!!

[etaf]

i dont usually do assignments for people on these forums , as that tends to defeat the objective of the assignment

if you can provide a manual diagram of the requirements
and then a sample of how the data is formatted
also lets be clear which part is the assignment

[labessade]

I understand that, I'm not asking you to do it for me! Just help me / offer advice so I can learn and know what is possible...

Thanks... I want to ideally do a line chart showing the 3 standard readings for glucose tolerance with an additional line for my case study patient data, but with the standard readings having a range between values I'm not sure if this can be done or the best way to achieve it. I'm bogged down with doing the research and typing it up at the mo which is the main part of the assignment being able to write 3000 words on the topic, but I'll try to knock something up later. I've attached a file I found online of a graph kind of similar to end desired result only with less data lines.

[etaf]

OK,
what you have showed is a simple line chart with 2 sets of data

if you can put the data into a spreadsheet and then a picture of how you would like to show those numbers - we can see what we would need to do

[labessade]

ok bear with me I've been playing all night with this and moved forward a little I think! A line chart won't work because there are only 2 data plots unlike lots like you had, my patient data has 5 plots but the standardised data to compare it will only gives you two. It looks like this (see attached) which isn't quite correct anyway as it doesn't show IGT as its too close to the other plot and its a range anyway between 6 and 7 for IGT plot 1 values so this can't be shown as far as I aware. I tried playing with it by adding another data plot to try to show range (graph1) but again this isn't really accurate. Also I wasn't sure how to get the values coming out of the y axis so I drew a line in to demonstrate this is what I wanted. I moved on to difference chart types which I will put in next post and I prefer the look of, the area chart or stacked columns are the closest pictorially to what I think looks right...

[labessade]

ok moving on... here's an image of some others I've been playing with. Almost there but not quite... the chart at the bottom has the correct axis values but it isn't pictorially correct as diabetes is above the line not all the way to 0 (like in the other 2 charts). I think either of the other two ones work well in that they seem to be the only way I can think of for showing a range eg 0 = 0-6 normal, 6-7 abnormal, above 7 diabetic and 2hrs = 7.7 normal, 7 to 9 abnormal and above 11 diabetic. BUT.. the axis values are incorrect and I don't understand why... maybe its because of the type of chart and this can just not be done how I want it??? Thanks for any advice...

[etaf]

can you also post the spreadsheet - so we can see the same data

The x-axis maybe because of the values you have selected to graph

[labessade]

I can but the data is literally just the ones I put on the last post... I will make a new version for you if you want with just those values as don't want to post all of it... it is correct as all the charts (inc the correct plots) use the same data.

[labessade]

ok here is the data I used to do these graphs, the data I have to go off to analyse is what I put in the last post with some ranges of 0 and 120 values but I'm not sure how I would enter this as a plot in excel, which is why I tried to get it showing the colour stages depicting a range. I'm wondering if the only way maybe is a scatter graph as there is so few points, in which case a table and chart will be better I think as its maybe not enough data to plot...

[etaf]

can you draw what you are after maybe -
or you said this was the type of graph - a column chart with x-axis - fasting/2hr

[labessade]

the images I sent are exactly what I want but they don't come out with the y axis having the correct values... the problem with your example is its a range. Diabetes doesn't start at 0... it starts at 7 and 2nd plot is 11.1 so it needs to show between these values like the stacked colour ones I sent you. The only other thing I can think of is I did see someone do a floating column chart but I have no idea how to even start one! I'll try to draw what I mean...

[etaf]

so i have set up a stacked graph - which only takes the difference between normal and diabetic

so the reference is in A6 to D9
and changed the value - so that the difference is not just 0.1 , as that may be an issue in this format

and then the calc is in A13 to D16

[labessade]

ooh that might be it, except for one little thing which might not be possible... there is no top limit for diabetes so it is possible to have that colour go all the way to the top of the graph for both columns? Actually if I add in the 2 values for the patient data that might not be an issue as they are higher values... I will have a look at tomorrow as I'm shattered and need to get to bed! Thanks a lot for this I will look at properly tomorrow

[etaf]

so i have changed d15 and d16 - so that the total of all three values in B15+C15+D15 = 14 - and set that as the top of the scale

I think you are missing that the values in a stacked graph are added together

So if A =10 , B=15 and C =20
then we have

the following stake

20
15
10

which means the scale is 45 on the Y axis

But you want the 2nd value to be 5

15 - 10
as the range is 0-10 and then 10-15

5
5
10

are the values you need to have to stack to show

probably not helped

to make the value float

all you need to do is change the colour of the normal set to NO FILL

see 2nd graph below the main graph

[labessade]

Thanks!! Yes I kind of thought it added values somehow but I didn't understand it. Thank you for the explanation I will try to get my head round it once awake . I changed the values in yours to the same higher values for both diabetes bits and this seemed to work. I just need to decide now if to add the patients data on top of the stack or maybe work out how to add a second set of data and have the two value points as a line although it would be above the stack so might look funny!

[etaf]

you could have a line across the sheet
or you add there value as a 1 - amount
and then include that in the stack - and make the stack higher than the actual value

so at the moment you have

7 - 14 (7)
5.9 - 7 (1.1)
0 - 5.9 (5.9)

so you can add another value in the mix

say the value for actual is 12

12 - 14 (2) - change the fill colour to be the same so its still GREEN
11 - 12 (1) - fill yellow
7 - 11 (4) - Green
5.9 - 7 (1.1) - red
0 - 5.9 (5.9) - blue

hope that helps