Hi, I was wondering why does the cell D23 suddenly end up with lots more numbers after the decimal place when I change one of it's input cells from 2 to 3 decimal places?
The D23 Formula is:
"+/- " & ABS(D7×D9×SQRT(DUR2DAYS(D15)÷365))
Cheers.
MacBook Pro 15″, macOS 13.4
Good question. I thought it followed the format of the cells (or maybe just the first cell) in the formula, like it was doing before you changed it to 3 decimal places. If you set cells D7 and D9 to the same number of decimal places it will use that format. If they are different, it ignores it and gives the full number with all its decimal places. I guess it wants them to all be the same.
Here is an alternative method that will allow you to choose the number of decimal places for the answer:
D23 =ABS(D7×D9×SQRT(DUR2DAYS(D15)÷365))
Create a custom format for it with the number of decimal places you want.
Thanks for the custom format @Badunit, you're right, D7 and D9 have to have the same nos of decimal places to get the Expected Move result in D23 to not have lots of digits after the decimal place.
This is not ideal because D9, Interest Rates aren't typically expressed to 4 decimal places, 7.7400%.
Also the D7 £/$ price needs to be written as 1.2718 or 12717 and not 1.27 (I only got as far as adding 3 decimal places when I took the screenshots above before noticing the huge number it produced in D23 (Expected Move).
Is there anyway to have D7 (£/$ price) and D9 (Interest Rates) have two different decimal places formats:
2 decimals for D7 and
4 decimals for D9?
Or is there something that can be added to the formula
"+/- " & ABS(D7×D9×SQRT(DUR2DAYS(D15)÷365))
that would make the D23 result stick to 2 decimal places?
In the screenshots the £/$ price will no doubt be entered differently by different users of the calculator and that's why D7 is written in different ways.
Cheers again,
How about ="+/- " &ROUND( ABS(D7×D9×SQRT(DUR2DAYS(D15)÷365)),2)
That did it! Cheers.
Change in Decimal Places creates a really Long Number?