Making charts equivalent

[jimbo]

We track the average patient value on a number of different analytes.
Is there a standard way to make the graphs appear equivalent? If I
track hemoglobin with a mean of 14.0 with the minimum set at 10 and
the max set at 18, and then I have a second graph for hematocrit that
has a mean of 38, but the max is set at 41 and the min at 35, the
latter is a steep graph, and the former is flat, even though the
hemoglobin values are approximately 1.3 the hematocrit.

But the setting of the axis values can potentially make data look
misleading. I am looking for a rule so that data will appear
consistant, whether the data is on hemoglobin, albumin, or ferritin.

Thanks for your help

 

[Jon Peltier]

Your first chart has min of 10 and max of 18, so the range is (18-10)/18 =
44% of the max.
Your second chart has min of 35 and max of 41, so the range is (41-35)/41 =
15% of the max.
Any comparable deviation will have 44/15 ~ 3 times the slope in the second
chart. If you want them to be comparable, your second chart should go from
3*10 (30) to 3*18 (54).

- Jon

 

[Brad]

Gosh, I feel awkward even suggesting another solution over Jon Peltier's, a
man I have borrowed so much from, I ought to be sending him monthly royalty
checks.

But, this comes to mind, that what you may wish to do is "normalize" your
disparate data. I do it all the time using something like this to force a
column of values into values between 0 to 100:

=ABS(AT207-SMALL(AT$207:AT$213,1))/ABS(LARGE(AT$207:AT$213,1)-SMALL(AT$207:AT$213,1))*100

I just grabbed this formula from a spreadsheet but you can see that I am
converting the first value in a range from AT207 to AT213 to how it "fits"
in a normalized range of 0 to 100. You'd place this formula in a column next
to your data and convert each datum to its ratio value between 0 and 100.

This will calibrate everything ratio-wise between high and low ranges that
differ from one of your readings to another. It won't work if you need to
maintain the actual readings in your graphic but it will iron out how one
reading compares to another between their respective bounded high and low
limits.

Cheers! Brad

 

[Jon Peltier]

Well, I accept PayPal!

Seriously, this is a good approach. As you state, it's good to look at the
variation in the data if you don't need to worry about the actual values.

You could also define better scaling algorithms than Excel uses. It means
you have to apply custom values to the axis scale limits, but that's not too
big a deal. I use a modification of Stephen Bullen's technique, which is
found in Professional Excel Development (his outstanding book, written with
Rob Bovey and John Green), and you could also find it if you search the
Google newsgroups. The benefit of this is that the axis uses real values, so
you don't need to convert in your head between 0% and the data minimum, etc.

- Jon