# Thread: Calculating 3 and 5 decimal points in cells

1. ## Calculating 3 and 5 decimal points in cells

Hello! Hopefully I am posting this in the proper place but my issue is that I am putting together a trading journal for stocks and currencies. On the journal tab in cell D there are 27 currency pairs. Each pair has a price that you can buy or sell the pair such as 1.23456
21 of the pair have 5 decimal points as shown but 6 pair have 3 decimal points such as 1.234 the 6 pair are as follows AUD/JPY, CAD/JPY, EUR/JPY, GBP/JPY, NZD/JPY & USD/JPY If it matters it is 3 decimal points when the second of the pair is JPY such as AUD/JPY. If the decimal place is not calculated for these 6 pair or the pair with JPY then it affects the outcome of cells G, H, I & J
I am looking for a formula that will automatically adjust the decimal point of the cells when cell D uses one of the 6 pair listed or the JPY as the second of the pair. I have attached the journal and hopefully I explained this properly. Any help is much appreciated, thanks!

Journal Template.xlsx  Reply With Quote

2. Your attachment is missing data validation lists. It's blank and does not work.  Reply With Quote

3. here is my attachment with data:

Help Book.xlsx  Reply With Quote

4. You can round to 3 decimals or 6 decimals, but your display will have to show 6 decimals (i.e. the cells have to all be formatted the same).

=ROUND(yourformula,IF(RIGHT(D2,3)="JPY",3,6))  Reply With Quote

5. Originally Posted by NBVC You can round to 3 decimals or 6 decimals, but your display will have to show 6 decimals (i.e. the cells have to all be formatted the same).

=ROUND(yourformula,IF(RIGHT(D2,3)="JPY",3,6))
NBVC thanks for the reply! I do not know how to insert my formula as I keep getting errors. How would I insert that? Here is my formula: =IF(G6="Long",K6-H6,H6-K6)*10000  Reply With Quote

6. =ROUND(IF(G6="Long",K6-H6,H6-K6)*10000,IF(RIGHT(D2,3)="JPY",3,6))  Reply With Quote

7. NBVC thanks for the effort! The formula worked on the first pair that wasn't JPY but then I put a JPY pair in and the calculation was 10 when it should have been 468. I don't know where it went wrong.  Reply With Quote

8. Maybe because the formula was referencing different rows, and also in your sheet it looks like the Long or Short is in column F, not G as per your formula?

Try putting this in your formula column in row 2:

=ROUND(IF(F2="Long",K2-H2,H2-K2)*10000,IF(RIGHT(D2,3)="JPY",3,6))

Make sure the cells in the formula are referencing the correct cells in the row for the formula and its logic.

Then copy down the formula.  Reply With Quote

9. NBVC I think all I need is for the formula to state that if it is a JPY trade then instead of *10000 it would be *100 because when I change the 10000 to 100 it is right. so using my first formula

=IF(F2="Long",J2-G2,J2-G2)*10000

how can we differentiate?  Reply With Quote

10. Perhaps:

=IF(F2="Long",K2-H2,H2-K2)*IF(RIGHT(D2,3)="JPY",100,10000)  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•