Quick Google sheets help

[audi321]

Hi all. I’m sure there’s an easier way than I can work out but how could I convert on cell which is in a fraction (ie 12 1/4) to a decimal in another cell (so it would show 12.25)? The denominator will always be the same (in this example 4).

Thanks in advance!

Edited by audi321 on Monday 8th August 10:37

[eps]

A shame if you haven't got access to Excel, which will handle this a lot better.

So... I would do something like this. The cell referenced depends on the cell(s) you are using.

1) in cell B2 =SPLIT(A2," ") this then splits the number into two parts, the whole and the fraction. e.g. 12 1/4 becomes 12 and 1/4 in separate columns

2) then add them back together again but calculate what 1/4 is i.e. .25

=SUM(B2,left(C2,find("/",C2)-1)/(right(C2,len(C2)-find("/",C2))))

Which adds B2, which is the whole part.

This might need some nuancing if there is only a fractional part... and no whole e.g. 1/2 on it's own.

e.g.

B column =IFERROR(IF(FIND(" ",A4),SPLIT(A4," "),0),0)

D column =IF(B4=0,SUM(B4,left(A4,find("/",A4)-1)/(right(A4,len(A4)-find("/",A4)))),SUM(B4,left(C4,find("/",C4)-1)/(right(C4,len(C4)-find("/",C4)))))

I had to format the column which holds the fractions as DD/MM - otherwise it tried to set it to a date field, numeric which ended up as around 44586 or something...

Ideally if you can get the incoming data to be decimals then converting the other way is a lot easier..!

[walamai]

Perhaps I'm missing something, but it seems like you can just do this with formatting.

In the cell to show as a fraction, choose Format -> Number -> Custom number format, then use '# ?/?' as the custom format. (without the quotation marks) That will show the number as a fraction.

Then in the other cell, just have the value "=[cell with fraction]" and change the number format to a decimal. eg. use the built in Format -> Number -> Number, or change the number of decimal places in a custom format as required.

[eps]

walamai said:

Perhaps I'm missing something, but it seems like you can just do this with formatting.

In the cell to show as a fraction, choose Format -> Number -> Custom number format, then use '# ?/?' as the custom format. (without the quotation marks) That will show the number as a fraction.

Then in the other cell, just have the value "=[cell with fraction]" and change the number format to a decimal. eg. use the built in Format -> Number -> Number, or change the number of decimal places in a custom format as required.

Have you tried this? Great if it works.

[audi321]

Thanks both, I have tried the easy option and it doesn't seem to work, it just keeps the value as a fraction.

The complicated way seems to be the only option......thanks for that....the good news is that there will never be a fraction without a whole number, so at least that's a bit easier.

Thanks again.....

[walamai]

Ahh, I think the thing I 'missed' was that your source data is actually text. So yep, eps's method is the way to do it.

My suggestion displays it on screen as a fraction and a decimal, but the actual data needs to be a number to start with.