How to format in stones & pounds and then present on chart

[Pete Harris]

I am trying to create a chart to track weight in stones & pounds over a given
time period. I have already looked at other posts on this but none of them
cover how the y axis on the chart can have 14 minor grid lines representing
the pounds between the stones grid lines, e.g. 14 stone 12 pounds on the 12
minor grid line between 12 & 13 stone.

Can anyone please help?

 

[Jon Peltier]

Here's what I would do. I'd calculate the values in the worksheet in stones,
so that for example 14 stone 12 pounds is 14.857143. I would set up the axis
scale so the major unit is 1 (stoner) and the minor unit is
0.0714285714285714 (i.e., 1/14). This gives 14 divisions between each stone.

If you want to show labels with stones and pounds, I don't think you can do
anything besides constructing the labels using text manipulation
functions.For example, if your weight in decimal stones is in A1, use this
in B1:

=INT(A1)&" stone "&ROUND((A1-INT(A1))*14)&" pounds"

If your weight is in pounds in A2, use this in B2:

=INT(A2/14)&" stone "&(A2-14*INT(A2/14))&" pounds"

If you want axis labels with mixed units, create your axis using stones as
your units, then make a dummy axis with an extra XY series, choose X and Y
so you get a data point where you want each label, and use data labels to
show these text (non-numeric) labels. See the tutorials here:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html
http://peltiertech.com/Excel/Charts/ArbGridLabel.html

- Jon

 

[Smezz]

On my later version of Excel I need a second parameter for the ROUND function so the 2 lines become:-

=INT(A1)&" stone "&ROUND((A1-INT(A1))*14,0)&" pounds"

If your weight is in pounds in A2, use this in B2:

=INT(A2/14)&" stone "&(A2-14*INT(A2/14),0)&" pounds"