I don’t understand what is going on here, hope some IT competent spreadsheet gurus can advise. I have a spreadsheet where someone input ratios such as 1:10, 1:100 etc. these have been converted to numbers. I might have expected these to equate to 0.1 and 0.01 respectively, but what I find is very different. Here is a table of what I put in and what I find:

Ratio Excel
1:10 0.048611111
1:50 0.076388889
1:100 0.111111111
1:500 0.388888889
1:1000 0.736111111

What do these numbers mean? What operation is happening to make this conversion? Does the ‘:’ symbol equate to some mathematical function? Is it something to do with Log transformations?

Any and all help gratefully received…

Looks like a 50/50 post to me 😉

Take a look at the cell formatting.

I think excel takes the colon to mean it’s a time.

If you want to enter it set the format to text.

As above, cell formatting. : is the separator for hours:minutes:seconds.

this is what happens when secretaries* use excel.

using a colon in a cell turns it into a timevalue. The numerical value in the excel database attributed to something with a : is of no use to you.

Use text handling functions (such as LEFT, RIGHT, LEN and FIND) to strip that ratio expression into it’s two components (divisor, dividend) then you can do your maths. If 1 is always your dividend, then that makes it faster.

* could be male, could be female. Definitely shouldnt be allowed near Excel

Yup 1:10 and 1:50 are recognised as time. If you convert to number format you get the decimals you quote. Not sure about the other 3 though.

Does Excel recognise ratios?

Ok, so 1:10 might be seen as 1 min 10 seconds and that is getting expressed as a decimal of 1 hour, but….
How does this work for 1:100? It can’t be 1 min 100 secs because that would be 2 mins 40 secs

IF you can rejig the spreadsheet so you put 1:10 in as two columns 1 & 10 you’ll save yourself a lot of grief.

Actually even that logic doesn’t work for 1:10

Got it! It’s treating the entries as hours and minutes and expressing them as decimal days

Thanks guys 8)

this is what happens when secretaries* use excel.

Yeah, you say that, but you’re happy enough for me to flutter my eyelashes and sit on your knee whenever I need help with my spreadthingies

this is what happens when secretaries* use excel.

using a colon in a cell turns it into a timevalue. The numerical value in the excel database attributed to something with a : is of no use to you.

Use text handling functions (such as LEFT, RIGHT, LEN and FIND) to strip that ratio expression into it’s two components (divisor, dividend) then you can do your maths. If 1 is always your dividend, then that makes it faster.

* could be male, could be female. Definitely shouldnt be allowed near Excel

And that’s what happens when you don’t know how to use the text to columns with : as the delimiter 😉

I need help with my spreadthingies

I’m here to help