Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Calculating 3 and 5 decimal points in cells

  1. #1

    Calculating 3 and 5 decimal points in cells



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,595
    Articles
    0
    Excel Version
    365
    Your attachment is missing data validation lists. It's blank and does not work.

  3. #3
    here is my attachment with data:

    Help Book.xlsx

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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))


  5. #5
    Quote Originally Posted by NBVC View Post
    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

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    =ROUND(IF(G6="Long",K6-H6,H6-K6)*10000,IF(RIGHT(D2,3)="JPY",3,6))


  7. #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.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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.


  9. #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?

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps:

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


Page 1 of 2 1 2 LastLast

Posting Permissions

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