0

I've created a formula to calculate the target profit-loss ratio of a stock trade in the stock market. However, the formula keeps giving me trailing decimal points. An example is:

Instead of 1. It says 0.999999999999999

I've tried formatting the cells and all that stuff. I've discovered that it's not a formatting issue but rather I think I just need to round the numbers. This means I need to include the ROUND() function somewhere in my formula to avoid these trailing 0's.

However, I'm having a hard time figuring out where the ROUND() function fits into my ratio formula.

I'll give you the exact Microsoft Excel cells and the numbers in them to help give more context to the formula I gave above.

  • M7 = $3.00
  • U7 = $3.20
  • V7 = $2.90

So, to put them into the formula looks like this:

=(U7 - M7)*10&":"&(M7 - V7)*10

I am multiplying the equations in the parenthesis because if I don't, it comes out as a decimal instead of a whole number, so that was a quick solution. It doesn't affect the trailing 0's because even if I take off the multiplication of 10, it still gives me a trailing decimal of 0.0999999999999999 instead of 0.999999999999999.

The premise and leading question is this: where do I put the ROUND() function into the formula so I can get rid of those trailing decimals.

Any solutions, even if it means a better more intuitive formula, will be amazing.

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Joshua
  • 57
  • 2
  • 8
  • I've revised it to get rid of `all that stuff`. Scott Craner answered without hesitation so thank you anyways. – Joshua Oct 27 '17 at 05:32

1 Answers1

2

You need to round the numbers before concatenating the string:

=Round((U7 - M7)*10,0)&":"&Round((M7 - V7)*10,0)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • THANK. YOU. SO MUCH! – Joshua Oct 27 '17 at 05:26
  • Consider to use `Worksheetfunction.Round` to avoid so called banker's rounding with possibly unwanted results (i.e. midpoint value 5 always rounding to the nearest even number ) - c.f. [How VBA rounds ...](https://stackoverflow.com/questions/46850958/how-excel-vba-rounds-doubles-to-integers/48042374#48042374) – T.M. Jan 20 '19 at 09:35