Excel Help - Number formatting

[valve90210]

I want to set up an excel spread sheet for recording weight, however I want it in Stones and Pounds. I've done some searching on google but not managed to come up with a way of doing this.

I want to be able to for example enter stone as whole number and the pounds as decimals if possible, for example if I wanted to enter 12 stone 11 pounds I'd like to just be able to enter 12.11 and have excel realise that that is more than 12.7 for example.

Once the number are entered I will also want to display them as a line graph, so I need the numbers in a format that Excel will allow me to use ina graph.

Can anyone help me???

Many thanks in advance.

Valve

 

[valve90210]

Right... slight change of help needed.

I have com up with a better way of entering the weights which makes life much easier, however, now I need to find a way of making excel not display anything if there answer to a formula is currently zero.

I have change my number entry to have one colum for stones and one for pounds, then in a third colum I have a formula which multiples the stones by 14 and adds the number from the punds column to give a total number of pounds.

However, I wanted to auto complete the formula down the whole total column to save me having to do it every time I fill in numbers. If I do this cos there are currently no figures excel returns a zero and fills this in all the cells, which is no good as I wish to use this column to create a graph, and if zeros are in the cells excel see them as a valid figure and it messes up the graph.

How can I prevent excel putting zeros in these cells so my graph works correctly???

thanks for any help.

Valve

 

[Fr0dders]

edit nvm mis read

only thing is to change the datarange of the graph to not include the cells with a value of zero

how big is your spreadsheet

if its only a page this shouldnt be too hard.

 

[valve90210]

Having even more of a problem now.

As I said I have a formula to convert stones and pounds to a total pounds column.

Now I can't get the graph to do what I want.

I think it's fairly simple but I can't for the life of me get it working.

I want to have a colum for the Date, which will be filled in weekly, and I want to fill in the stones and pounds each week. What I want to happen then is for excel to convert the stones and pounds into a total weight in pounds and display it on a line graph with the weight in pounds along the y Axis and the date on the x axis.

I only want it to display information I have put in, ie I don't want any zero data in the spreadsheet or the graph, and I want the graph to update when I fill in more figures each week. I have tried everything I can thin of but it's not working right. If I try to select the total weight column as the data range, I just end up excel telling me that it can only display 30000 or so entries cos it thinks it needs to display the data from the whole column.

And if I I try selecting the date column to go on the x axis, I get the same problem.

I can't believe that what I am trying to do is impossible, or even that hard but I just can't get it working.

Any help would be very very much appreciated.

Valve