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!

View attachment Journal Template.xlsx

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

here is my attachment with data:

View attachment Help Book.xlsx

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 said:

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))

Click to expand...

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

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.

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.

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?

Perhaps:

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

NBVC said:

Perhaps:

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

Click to expand...

NBVC that's the ticket! Your help is very much appreciated. Thank you!