## Bland Altman plots

MS EXCEL

### Next

• 1. Graphing date related data-points
Excel 2003 SP2 WinXP Pro SP2 I have an Excel spreadsheet with about 4000 rows and 52-ish columns. The top set of rows 1-13 are descriptive in nature. The general format of the spreadsheet is A: Day of week (Monday-Friday etc) B: Actual date C-E: Captured data F: Useable Calculated Data (formula #1) G-I: Calculated Data J: Useable Calculated Data (formula #2) K-L: Calculated data M: Useable Calculated Data (formula #3) O-Q: Calculated Data R: Useable calculated data (formula #4) etc... What I'd like to do is to see a graph whereby the Y-axis is related to the Useable Calculated Data and the X-axis is the Actual Date (Column B). Some of the useable calculated data varies from 0-1 (something akin to probabilities) and some of the Useable Calculated Data varies from 0-5 (a smoothing calculation). So a dual Y-axis representation with colors assigned to the various Useable Calculated Data columns would be very helpful here. That is, the left Y-axis could be 0-1 based and the right Y-axis could be 0-5 based. If this isn't such an easy or good idea, then I'll deal with it. I'm attempting to see the data displayed in a flowing 'sine-wave' format and to determine if the peaks and valleys coincide in the Useable Calculated Data columns. TIA!!!
• 2. =sum
Can I ask in a formula to not add a number in an array of a certain colour until the colour is changed. eg unpaid is in red, when paid change to black.
• 3. Percentages in a pie chart
I am trying to create a pie chart that shows the percentages of each type of monthly expenses for a household. I have set up a spreadsheet that contains the category name, the amount of each bill and the percentage that each bill represents of the monthly income. The problem is that when I create the chart, the percentages shown in the pie chart are different than the percentages shown on the spreadsheet. I know that the chart is updating automatically from the numbers on the spreadsheet because when I change any of the info on the spreadsheet the percentages on the chart change also but they still aren't right. I have tried to create the chart by including the percentages that have been figured in the spreadsheet and I have tried to create the chart by not including the percentages and it doesn't seem to have any effect on what the chart come up with in regard to the percentages it shows. Is there some kind of formatting that I'm doing wrong or anything.... I haven't done much with charts but I can usually figure most things out by playing around with it but this one is driving me crazy. Any help would be much appreciated......
• 4. "United Way" Style Campaign Thermomoter Fundraising Graph
I am trying to do an narrow bar graph for a fundraising campaign, % of goal. My version of Excel is 2003.
• 5. Standardize the y axis
I have created a dashboard that plots the top 5 cost drivers for the time period, for the data set that I am analyzing. However the maximum value of the cost drivers in all the time period differ greatly. How am I able to relate all the graphs on the page together so they share a common y axis range? Thank you

### Bland Altman plots

```Does anyone know if there is an add in that creates Bland Altman plots?

Thanks

John
```

### Re: Bland Altman plots

``` JRD  wrote  on Sat, 18 Aug 2007 07:30:02 -0700:

J> Thanks

It does not seem that it would be too difficult to set up Excel
to do what you want. See the Wikipedia stub on Bland Altman.
That's just what I did since I had not realized that Bland and
Altman had arrogated the standard Tukey mean-difference plot, as
is it is still known as in other fields.

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

```

### Re: Bland Altman plots

```I didn't follow the previous discussion, but here's a ready-made macro
solution (from 2004):

http://www.**--****.com/

For several other Excel solutions for statistics, related info and links,

http://www.**--****.com/

and follow the link on the left to Software (and scroll down to Excel
workbooks).

Regards,
Gaj Vidmar, PhD
Univ. of Ljubljana, Fac. of Medicine, Inst. of Biomedical Informatics

```

```My spreadsheet has 250 rows and A thru AG in columns, all cells are bordered.
All of the columns are used if a row is used.  My sheet has 15% more rows
than our highest use this year so that I am sure our office does not run
short of rows (my workbook has 12 of these sheets because we track the info
monthly and forward the sheets as filled out to other personel).

My question is how do I print the sheets (only one month is printed at a
time) to print only the rows that has been filled out, all of the sheet rows
are continuous, we have no unused rows except for the ones left at the bottom
that are unused.  when ever I print the sheets it always prints a few extra
pages from the bottom that are just the gridding.  I don't want to have to
delete all of the unused row before I print the sheet.
```

```Hi,

I looked around other postings but could not find the answer -- what I'm
having a problem with is thta in my data validation list, I have a range set
(which refers to column H) -- the number within the range can change, and I
sort it every time it does change so that there are no spaces between values.

The problem is that there are numerous blanks in the list at bottom, after
the last actual value in the range.  Is there any way to get rid of those
blanks?  I tried deleting (delete shift up) all blank cells after the last
value and through the last cell (H65536), but this doesn't help.  Below is my
code.  Thanks!

With .Range("B2").Validation
.Delete
xlBetween, Formula1:="=dvrange"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ShowInput = True
.ShowError = True
End With

--
Robert
```

```I have a spreadsheet that has a chart tab with a plot of data from another
part of the spreadsheet. Now, however, the chart is empty and when I try to
send any plot to a chart tab the plot is lost and the chart tab is empty.
This is happening now in all my spreadsheets the plot can be created within a
spreadsheet but as soon as I move it to a tab the plot is lost and the chart
tab has nothing in it.  !!!????!!!!
```

```A 2003 file being opened in 2007. Contains a line graph. I wish to remove a
plot line between two points. The lines right now or dotted, when I go
through the process of entering into the chart options and click on "No plot
line" nothing happens and the dotted lines remain. ANy suggestions? Or is
this a converting issue going from 2003 to 2007?
```

```I have a HP designjet 110 nr plotter. I've created a plot that we nee
to show drilling activity. My first plot was 24"x36". Plot works grea
and to scale. My next plot was to be 24"x72". Now the charts will no
even display in Print Preview. I can scale back the plot where it i
about 48" and it will preview and plot, but I need it to go out eve
futher than 72 inches. I may go out as far as 30 feet.

I have images on the plot area and it will print it just fine at th
farther distances

```