Column/Bar Chart Displaying Stones/Pounds

[Pejayuk]

Hi,

I have been trying since yesterday to add a simple column or bar type chart to a worksheet that I will be using to record weight loss. I just need the first series to display the target weight and each week to add a new column in series 2 for current weight to show progress.

The problem I cannot resolve is how to get the chart to display stones and pounds correctly. Is there anyway I can get it to show that there are 14 pounds in a stone?

Sorry if this question has already been asked, I have spent hours searching but having little knowledge of Excel, cannot find anything that I can understand.

Many Thanks,

Peter

 

[Gerald Higgins]

Here's one way.

You need to have each weight entered into a single cell, so for example 12 stone 7 pounds would be entered as 12.5

Right click on the chart's Y axis, and go into the Scale tab.
Untick the option for Auto on Major unit, and set this to 1, so this will be your marker for stones.
Untick the option for Minor Unit, and set this to 0.0714285714 - this is the same as saying 1/14.

Then go to the Pattern tab, and make sure that BOTH major tick mark type AND minor tick mark type are set to something OTHER than "none".
In other words, make sure that both major and minor tick marks are shown.

You will now get the NUMBER of stones shown, and you will also get a tick marker for each pound, although you won't see the NUMBER of pounds shown.

 

[MarkVMcCullagh]

Try formatting the numbers as fractions in fourteenths

I don't have Excel here but I believe its at:

Format >Cells >Numbers >Custom

0 0/14

Look at the Built-in Fractions Examples

 

[Gerald Higgins]

Great idea Mark !

Unfortunately some numbers look a bit clumsy in that format. For example 10 stones exactly appears as 10 0/14.

Is there a format that would improve on this ?

 

[Pejayuk]

Hi,

Thanks Gerald and Mark for the replies. I've tried both suggestions and although not exactly what I was after in the way of display clarity, they do both do the trick and are better than anything I came up with.

With the fraction formatting idea, I've been experimenting and discovered that #"s" ??/14"lb" looks pretty good. Wish there was a way of hiding the /14 part but it doesn't really matter.

Thanks again,

Peter

 

[MarkVMcCullagh]

Enter your first value in A1 and post this formula in B1:

=INT(A1)&" ST "&IF(ROUND((A1-INT(A1))/(1/14),0)=0,"",ROUND((A1-INT(A1))/(1/14),0)&" LB")

Drag the formula down in column B and use as the basis of the chart labels

 

[Pejayuk]

Thanks for the formula Mark,

It looks fine on the sheet. When I create the chart though, I can't get the formula I have copied down B1:B12 to display up the left side of Column Chart.

I open the Source Data box and I've set the values A1:A12. Problem is, there only seems to be an option to specify a range of cells for the (X) axis. Is there a way around this so they can be for the (Y) axis?

Thanks again,

Peter