Forest plot with uneven confidence intervals

[KarenLeukenhause]

I am struggling with finding out how to: 1) Make a forest plost in excel and 2) how to use uneven confidence intervals. Would anyone be able to help me? See below for example data:
CIupper SIR CIlower
Hospital 1 0.072 0.265 0.679Book2.xls
Hospital 2 0.007 0.27 1.505
Hospital 3 0.008 0.321 1.788
Hospital 4 0.042 0.346 1.249
Hospital 5 0.24 0.351 0.496
Hospital 6 0.009 0.36 2.005
Hospital 7 0.05 0.41 1.481
Hospital 8 0.011 0.439 2.447
Hospital 9 0.248 0.443 0.73
Hospital 10 0.324 0.474 0.669

SIR is an indirect standardization method outcome.

Thanks,
K

[davesexcel]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[MrShorty]

I don't really know what a forest plot is, but I fully expect if you explain it to us in enough detail, we can help you program it into Excel.

You might try putting "forest plot Excel" or similar into your favorite internet search engine. I found several hits, including one that claimed that a forest plot is a relatively easy adaptation of Jon Peltier's box and whisker diagram http://peltiertech.com/WordPress/exc...ams-box-plots/ See if that helps you get started.

[KarenLeukenhause]

I am new to this site and my understanding of how to link databases in minimal. I have added a spreadsheet to the original e-mail called "Book2". Within it is the same information with the wanted outcome. I appreciate both of your responses. To the Peltier's box, the problem is the uneven CI's. In most box plots or meta-analysis, the CI's are even on both sides of the data point. Thus, my issue.

[MrShorty]

Except that I thought Peltier's example also showed at least a couple of the data points also with uneven CI's (error bars) and that he explained how to add custom error bars for both the positive confidence interval and the negative confidence interval. It seems to me that, if your only concern is uneven error bars, that you just need to review the part about how to add and format the error bars to see how to add custom values for the error amount.

If it helps, here's the 2010 help file for adding and changing error bars (see the section "change the error amount options" #3). http://office.microsoft.com/en-us/ex...159.aspx?CTT=1

[KarenLeukenhause]

Yes, Peltier would work if I had the corresponding statistics. I only have the SIR and the CI's given to me. Not quite sure how to back track to the Q1/Min & Q3/Max values. Thoughts?

[MrShorty]

Not quite sure how to back track to the Q1/Min & Q3/Max values. Thoughts?

I'm not a full fledged statistician. That said, I'm not sure how to compute individual quartiles from only max, min, and "average"/"median". I'm not even sure if it can be done (rigorously). It seems to me that we need more information (maybe even the original raw data) to get accurate quartiles. Maybe if you had standard deviation/variance that was used to compute the confidence intervals and could assume a normal distribution?? This might need to be a question for someone more qualified in statistics. Peltier's example started with the raw data, so he could compute the quartiles directly from the data.

Perhaps once we figure out how to correctly compute accurate quartiles, creating the forest plot may not be so difficult.

I added to your spreadsheet. I just randomly guessed for the quartiles to create the "boxes". This should help you start to build the plot you want.

[KarenLeukenhause]

The CI's look near correct. In the graph, I need a way to identify the SIR value. When I create a new data field for the SIR and try to change the chart type to scatter for just the SIR value, the entire graph changes too. Is there a way to have both the bar and scatter graph together?

[MrShorty]

In the "adding the mean" section of Peltier's tutorial, he describes how to add a data series for the mean and how to format it so it is a different chart type. I would expect the same basic approach could be used to highlight the SIR value in your plot. If you need a different discussion about creating combination charts, here's the Excel help version: http://office.microsoft.com/en-us/ex...419.aspx?CTT=1

[KarenLeukenhause]

Nice! I have a lovely graph. Thank you so much for your direction and guidance. Big Thanks!

[FDibbins]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[KarenLeukenhause]

MrShorty. I have been unable to recreate your confidence intervals (error bars) with the example you sent. Could you possibly elaborate on how you created the confidence intervals? I have tried to use Peltiers, but I get the same issue. I've attached my initial attempt.

Thanks,
D