Function/Formula to display Stones/Pounds/Ounces

Hello

I have a spreadsheet to track weight loss over several weeks for colleagues in the office. The weights are currently entered in stones/pounds/ounces like this 13.10.2. A weight will be entered each week to track progress. I'd like a way to calculate the total weight loss at the end of each month (some kind of formula or function) which automatically updates when a new weight is entered each week.

Also, is it possible to use conditional formatting on the total monthly loss to highlight the greatest and lowest loss?

A copy of the spreadsheet is attached for anyone who would be kind enough to help me. Thanks in advance!

Please find attached

Thanks for your responses. As I said, the weights entered need to be displayed as Stones Pounds Ounces rather than as a decimal. I want to be able to enter the weekly weights rather than having to convert to decimal.

Originally Posted by Richard Buttrey

Hi,

Does the attached help.

I'll look at the conditional format later.

This would be easier to do if the weights were in metric, e.g. Kg. Is that an option?

Thanks Richard, this is a great solution. Yes you're right, metric weights would be easier but the scales they are using are in imperial as that seems to be what everyone's used to and it's a pain having to convert etc.

Originally Posted by Mohamed.Mohyeldien

Please find attached

Just wandering if there is any easy way to use the Man of the Month formula that Mohamed has used in the solution that @Richard Buttrey came up with? Would be good to implement this in if possible? Thank you

Originally Posted by Richard Buttrey

Hi,

Can't think of anything simple at the moment. The difficulty is that as presented any formulae are having to work with strings, and since CF calculations require numbers then interpreting stones.lb.oz strings first as numbers and then using in calculations will need formulae that are quite long. I'm not saying it can't be done, just that the formulae will not be very elegant and certainly hard to read.

I think the pragmatic solution would be to use a 'shadow' sheet which is linked to the current sheet but which first converts the strings to numbers. Then the calculations are simple.

See attached

Great work Richard, thanks very much. So with the shadow sheet, have you converted the weights into kgs to then use in the formulas to display them? Would I need to do this for each of the following months in order to replicate the layout?

Thank you