Hello All You Excel Peeps!

[bohemiancoast]

So. We're doing charts in Excel as our Sunday morning family project. Along the X axis, we have the weeks from now till the summer. Along the Y axis, we have our weights. No problem with maximum, minimum, major intervals (7 pounds), minor intervals (1 pound). How do I get the Y axis labels to say "14 st." or "14 st. 7lbs" -- ie, how can I tell it that the number format should be stones and pounds?

Oh, yes, I know we only use that format in the UK. But frankly, Microsoft have a great big customer base here and weight charts isn't exactly an uncommon use of spreadsheets, is it?

I do love the chart formatting in this version of Excel though. I'm pretty sure that I can't go into my work version of Excel and define the exact spot size, stroke and fill, shadow... mind you, roll on Apple spreadsheets, which I am sure will be MUCH PRETTIER.

Comments

[bugshaw.livejournal.com]

Type the text you want for the labels into a column in Excel and use that column for the labels?

(I've never actually tried this...)

[andrewducker]

Use a formula. You can write something in VBA in a few minutes that will convert pounds into stone/pounds and return it as a string.

[bohemiancoast.livejournal.com]

Except that I need to then use it as chart values on an axis, and the Excel charting needs to plot intermediate values. Do you think it will work for that?

[armb.livejournal.com]

Use weight in pounds for charting, and have another column that converts that to text using stones for labels? Googling, I can see people using a stone column, and pound column, and a (14*A + B) column, which might make entry easier.
Do it all in kg? :-)

[andrewducker]

Wow, I pop back after a few hours and discover that that's a _lot_ more complicated than I thought it was going to be.

Glad there are some very experienced people on here :->

[annafdd.livejournal.com]

here comes the expert - i do this every night.

Select the Y axis, either by clicking on it or by choosing "Value Axis" from the drop-down menu in the "chart" strip menu.

Right-click or command-click on it (or choose Format from the applicaiton menu), a menu pops up. Choose Format Axis.

In the window the pops up, choose "Number".

Select "Custom".

At this point, start from one of the options - for weights, you might start with 0.00 or delete a zero and choose just one decimal.

Now in the little window just above the list of options, put the cursor after the number and type "st" (include the ").

At this point, Anna notices the further complication of the lbs. Ouch. There must be a way to make the lbs appear... Well... 0"sts".00"lbs." does the trick, assuming that 10 lbs = 1 stone, which this Metric person doesn't know. If it's not, I'm afraid you have to resort to very, very complicated formulas.

[annafdd.livejournal.com]

Ok... now I know how many lbs is a stone. Allright... then the solution is tiny but more complicated.

Enter stones and lbs in two separate columns, B (stones) and C (lbs) - I will assume for semplicity that A is the column with the week number. Now, in column D, you type: =TEXT(B2,0)&"st. "&TEXT(C2,0)&"lbs."

Now, making this the Y axis.... I don't think that's possible. :-(

[del-c.livejournal.com]

It can be done, but only by one of a number of different cheats, all of them awfully complicated. Here's one that doesn't require any Excel add-ins or VBA.

First, let's take care of turning a number in pounds or stone into text. If your numbers are in pounds, create a range in pounds starting with your desired Y-axis minimum, ending with the maximum, and having the values you want along the axis. Then use the formula

=SUBSTITUTE(TEXT(B1/14,"?0 ""st."" ?0/14"),"/14","lb")

to convert into the form "13 st. 7lb", etc. B1 is the cell where the initial value in pounds is. If you're working with stone, use B1 instead of B1/14. Or you can just write the text in, it's not as if this is going to have any connection to your actual data, it will be completely a dummy axis.

To the right of the text, add a range of cells with zeroes in (or any number you like; it's just a dummy. But zeroes keeps it simple). Now add the text labels and the zeroes to the chart as another series. It will look weird at first, but persevere. Select the new series, and change the Chart Type of just that series to "Horizontal Bar Chart". You now have what's called a combination chart-- some of the series are one type, and others are of another. The thing about a bar (not column) chart is that it has the category axis *down the left side*. This is the only time you'll see text labels on a "Y" axis, because this is of course the bar chart's X axis.

The remaining steps are easier to do than to describe. Ensure that the stone labels match up with Y axis of the actual data-- you must do this by hand, because there's no logical connection between them, it's only for presentation purposes. Then format the dummy series so it's invisible, eliminate the series from the Legend (taking care not to actually erase the series; it must stay, but without being seen).

Because the combo chart has two X and two Y axes, format the second X axis, the one on the top, so that it's either invisible, or pleasingly matches the one on the bottom. You can get rid of it, but sometimes you wish you hadn't, so best to leave it on the chart, either invisible or making itself useful.

Because the two Y axes are both on the left, they can interfere annoyingly with each other when you're trying to change one, so use the Primary X axis properties to move the Primary Y axis over to the right, by checking the "Value (Y) axis crosses at maxmimum value" box in the Scale tab. When you've ensured the two Y axes match numerically, you can, again, either delete the primary Y axis, format it invisibly, or format it aesthetically.

I think that's the lot. Here's a web page that may help, by Charley Kyd. It's for a completely different kind of chart, an interesting form called a "dot plot" that Excel can't normally do. I mention it because it uses the same "bar chart" trick to put labels down the side of a graph, which is otherwise impossible.

Or you could follow papersky's recommendation, and just use pounds or kilograms; it's less hassle in the end.

[bohemiancoast.livejournal.com]

That is incredibly clever, and far more work than I had in mind for what is, after all, merely charts for the kitchen wall of a house full of tubby people.

But thanks to everyone for their hard work in sorting it out, and big Boos to Microsoft for not letting me format a number as stones and pounds.

[papersky.livejournal.com]

If there were ten pounds in a stone, we'd never have needed to go metric.

Weigh yourself in kilos, bohemiancoast! Not only does it make the arithmetic easier, but it's guilt free weight as well.

[bugshaw.livejournal.com]

Hello again! I was Googling for ways to do stones and pounds in an Excel chart and it brought me here :-)

I have had a bit of luck with pivot tables, dates against weight in 13:08 format, but it's not pretty. You can instantly see how many times your weight measurement has been x, though.