For a school assignment I'd like to create a Hertzsprung-Russell Diagram of a set of data of nearby stars plus stars in Orion. I've made a first stab at it using Excel, but there are problems. ...

The data I have is a table of stars giving me their spectral type and M

_{v}. (I also have names/designations, but they're not for the graph.) M

_{v}(absolute magnitude) is a number so it works just fine in Excel. But the spectral type is a complex alphanumeric and Excel is not dealing with it correctly. Here's a small sample of the data so you can see what I'm up against...

Code: Select all

`BD+44°2051A M1 V 10.40`

Kapteyn’s Star M1 VIp 10.89

CD–25°10553B M1.5 13.80

a Ori M2 Ib -5.14

Even if Excel understands the alphanumeric nature of the spectral type column, I'd end up with a horizontal scale which is dependant on the relatively small amount of data I have, rather than being comparable to standard HR-Ds which are derived from data on many tens of thousands of stars.

I thought of a way to get the spectral type scale consistent, by inputting a fake data series which uses all possible spectral types, and giving that series false M

_{v}data to make sure it's off the chart. But even that won't work, since the standard spectral type scale is not linear, and my false data would be.

So at this point I'm stuck. I've tried searching online for the numerical methods used to build an HR-Diagram, but if it's out there it's lost in the millions of Googlehits. So naturally I turn to the pool of experts here at APOD. Can anyone here lead me to instructions and algorithms for creating a suitably formatted HR-Diagram? If possible I'd like to do this in Excel, but if that's not possible I can try working with other graphing packages. (I use Mac OS X by preference, but I

*can*use Windows if the alternative is death by a thousand cuts.)

Rob