Any Excel gurus out there?

[egomeister]

Just trying to represent some data graphically using excel but having a few problems. Basically the data is of the following format:


Height Weight
Dave 5'10 75kg
Tom 6'0 95kg
Joe 5'6 65kg

What I want to do is plot height on the x-axis against weight on the y-axis then have each individual data point labelled with the correct name. I'm sure its not hard to achieve but I just can't figure it out! Anyone know how to do this?

[havoc]

I was typing a reply, then I tried it and realised your chart isn't one of the standard excel forms. So it's not going to be straightforward.

You may be able to customise it, if you're good on Excel. Equally you may be able to find an example on the web and bastardise that...

See what Excel online help offers first (may have links), then maybe google...

[canam-tt]

The best thing you can do would be to convert the heights to inches or metres?

[Raify]

I think you need two axes, one on the right for Height, one on the left for Weight and the names along the bottom.

And yes, convert the height into inches.

[BliarOut]

Or mm. you're mixing metric and imperial data.

[boiler]

Never figured the 'proper' way to do this, but have worked out a workaround if you don't have too many points...

You can use the XY scatter graph to plot the points, although you have to set it up manually to have a series with one range on X axis, and one on Y axis. Unfortunately, I can't get Excel to display the labels (you can get either of the ranges to be displayed, but not a third column). To get around this, I add text labels. Certainly not perfect, but it works for me, as I am only dealing with 8 or 10 distinct points.

Andy

[GreenV8S]

Create a separate series for each point (not THAT much harder than creating all the points in a single series) then select the series name to be displayed with each point.

[egomeister]

Thanks for the suggestions guys - the data was only an illustration so units is not a problem.

I had another go at it last night and came to the same solution as GreenV8S, ie and each series individually. Unfortunately I have rather a lot of data to display so this may take me some time unless I can find some way of automating the process. (I basically set up a X-Y scatter chart and added new series where the "Person" was the name, the cell containing "height" was the X-Value and the cell containing "weight" was the y-value - this process then repeated for each data point)

I wouldn't know where to begin on automating this - any suggestions?

[ATG]

egomeister said:

Thanks for the suggestions guys - the data was only an illustration so units is not a problem. I had another go at it last night and came to the same solution as GreenV8S, ie and each series individually. Unfortunately I have rather a lot of data to display so this may take me some time unless I can find some way of automating the process. (I basically set up a X-Y scatter chart and added new series where the "Person" was the name, the cell containing "height" was the X-Value and the cell containing "weight" was the y-value - this process then repeated for each data point) I wouldn't know where to begin on automating this - any suggestions?

Horror beyond horrors ... Tool>Macro>Record New Macro ... once recording, add another person to your graph. Once you've done that, stop recording and go to the VBA editor to see what has been recorded.

[vee]

I have a file with the relevant VB code.
While I'm pretty advanced on Excel, this was beyond me but a work colleague managed to set it up.
You simply put in 3 columns (Names, Data1, Data 2), click a button & it will do what you want.

PM me your email address and I'll send you the file.

Regards

[dilbert]

egomeister said:

Thanks for the suggestions guys - the data was only an illustration so units is not a problem. I had another go at it last night and came to the same solution as GreenV8S, ie and each series individually. Unfortunately I have rather a lot of data to display so this may take me some time unless I can find some way of automating the process. (I basically set up a X-Y scatter chart and added new series where the "Person" was the name, the cell containing "height" was the X-Value and the cell containing "weight" was the y-value - this process then repeated for each data point) I wouldn't know where to begin on automating this - any suggestions?

Are you saying that your source data is in a text file format?

Overall, you just need two columns of data, one for each axis. Select the lot, and select <insert> <chart> then choose "XY (scatter)" from the menu.

If the data is in text format, then the easiest thing to do is to use a text editor, with a column selection facility. Textpad, at www.textpad.com, is a good example.

Use the column selection facility to neatly format the data with a known delimiter.

Common formats are CSV, comma separator variable and tab delimited.

You can then use the text import facility of excel which will pick up on the tabs or commas and return characters. It allows you to set the excel columns in the text data.

As for the height in feet and inches, you will need to use a macro. Once you have the raw data in columns, you can write a small macro, that spins through each of the fields and converts base 12 units to base 10.

Basic idea would be to scan the field for all characters up to the <'> and convert from a VB string into a long. Then do the same after the <'> and convert that to another long. Add the two together and dump them out to another column on the same row.

If you get past the first bit, post again, and I'll knock out a macro for you.

>> Edited by dilbert on Friday 25th November 11:07

[Whoozit]

Our expert dpt-type people say the only way to do it in Excel is to manually add the labels. It's something they particularly hate us for doing cos it takes a very long time.

Just one more example of why monopoly software is a bad thing.

[vee]

Whoozit said:

Our expert dpt-type people say the only way to do it in Excel is to manually add the labels. It's something they particularly hate us for doing cos it takes a very long time. Just one more example of why monopoly software is a bad thing.

Wrong, it is possible ot add labels automatically, just requires the use of code - which I wasn't able to put together but do have a copy of !

[mmm-five]

As a long time DTP & Document Production person we tend to do the manual labeling for less than 10 categories (names in this case). We hate it, but we charge by the hour so the longer it takes, the more money we take in. However we tend to use Deltagraph for the more complex graphs as these as you can have a 3rd column/row containing the labels you want. We usually then copy it to Illustrator to tart it up, but the basic chart is there.

This is an example of the basic chart we start with...


>> Edited by mmm-five on Friday 25th November 11:43

[dilbert]

mmm-five said:

As a long time DTP & Document Production person we tend to do the manual labeling for less than 10 categories (names in this case). We hate it, but we charge by the hour so the longer it takes, the more money we take in. However we tend to use Deltagraph for the more complex graphs as these as you can have a 3rd column/row containing the labels you want. We usually then copy it to Illustrator to tart it up, but the basic chart is there. This is an example of the basic chart we start with... >> Edited by mmm-five on Friday 25th November 11:43

I'd say that Lucy and Trevor have had too many pies!


Edited to add;

I'd better make that bob!


>> Edited by dilbert on Friday 25th November 12:10

[406tm]

dilbert said:

I'd say that Lucy has had too many pies!

Ah, but she is 3ft 6in tall

[mmm-five]

Lucy is 6ft 6inches and 80kg so a bit lanky I think! Trevor is 4 inches taller and about 16.5 stone. If you're not careful I'll send short, fat Bob around to eat all your pies (he's a 'small' orangutan at the zoo I did this for).

>> Edited by mmm-five on Friday 25th November 15:31