You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I've tried to get the ".00" to disappear so the ratios don't read 3.00 : 5.00 or 1 : 5.00 but can't figure how do do it?
Cells K5 and K7 are currently:
ROUND(I5÷J5, 1) & " : " & J5
"1:"&ROUND((I7÷J7),0)
I need the decimals in columns I and J though?
Cheers!
MacBook Pro 15″, macOS 13.4
Something like this?
=TEXTBEFORE(I2/100,".")&" : " &TEXTBEFORE(J2/100,".")
Replace the , in the formula with ; if your region uses , as the decimal separator.
Depending on your data you may or may not want to divide by 100.
SG
If you set the decimal places in columns I and J to zero, the result in your formula will also be zero decimal places. It will round the result to 0 decimals without needing to use the ROUND function. Both columns need to be the same or you'll get a lot more decimal places than you have now.
You have your ratios formatted different ways in your screeshot and I do not know which you prefer. Here are two possibilities:
=I5/J5&" : 1"
=I5/100&" : "&J5/100
In Excel, when you use ROUND it will format the result with the specified number of decimal places. In Numbers, the number of decimal places depends on the format(s) of the referenced cell(s). When the end result is a number, you can simply format the cell so it displays properly, but when the result text you cannot.
So another way to do it if you like the 5:1 kind of format is to create a custom format. The formula will be a simple numeric formula of =I5/J5 . The custom format will look like this:
Cheers, I'm not sure why the Custom Ratio formula didn't work although Numbers took a while calculating It, it still came out without any change to the cell...
SGIII's solution worked fine, however, I also got it to work as a simple division as you mentioned by using your customer format, after I realised the risk and the reward columns needed "-" and "+" signs.
Problem now is I have another column where the denominator is not fixed and so I went to create another custom ratio format because the first custom format is using. "1" and the math is wrong when dividing columns I and J because it's gives an incorrect ratio of 2:1, but was faced with dates in the format tool!?
Thanks, I should have left the "?" off the comment (I need the decimals in columns I and J though?) as it sounds like I'm asking if I need the decimal places which I do. It would make things easier as this is hard getting this to work.
I can't get the two possibilities to look like 5:1 or the customer format (from cells with 500.00 and 100.00)?
Bardonicloud wrote:
look like 5:1 or the customer format (from cells with 500.00 and 100.00)?
See my latest attempt, which does give you 5:1.
=TEXTBEFORE(I2/J2,".")&" : 1"
Or,
=TEXTBEFORE(ROUND(I2/J2,0),".")&" : 1"
SG
If the formula in L2 is simply = J2/K2 and you use the custom format, you will get ratios like 3:1, 5:1, etc.
I'm not clear on why you need the ":1" tacked onto the number. It doesn't add any useful information. Nor does dividing each number by 100 and putting a ":" between them give you any more information than what is already in columns J and K . It is actually a far less accurate representation because it is highly rounded (and you can have identical ratios like 5:3 and 10:6 that don't look identical without doing math in your head). A straight up division of J/K with a few decimal places would be a better number than any of the methods that have been proposed for a ratio.
Thanks SGIII, these codes work without the repeat issues I had in L10 and L11 in my previous screenshot.
Thanks, I got repeat ratios? Yet each row down matches the J3 and K3, J4 and K4 etc references.
TEXTBEFORE(J3/100,".")&" : " &TEXTBEFORE(K3/100,".")
Is something like this what you want?
=TEXTBEFORE(I2/J2,".")&" : 1"
Or perhaps this (which rounds to nearest integer)?
=TEXTBEFORE(ROUND(I2/J2,0),".")&" : 1"
SG
Remove .00 in a Ratio
