+ Reply to Thread
Results 1 to 3 of 3

Currency in decimals and the sum is wrong, complex formula

  1. #1
    Registered User
    Join Date
    09-25-2019
    Location
    London
    MS-Off Ver
    16.27
    Posts
    2

    Currency in decimals and the sum is wrong, complex formula

    Hi guys,
    Please help!!!!

    I tried to find the solution for my problem but one of the previous examples explained quite a simple formula and mine is a bit complicated.

    Input: =IF(F12<>"",(IFERROR((G12/VLOOKUP(F12,$G$3:$H$9,2,FALSE)),"Input XR")),"")

    Where exactly should I insert the Round option as it's been the whole day I was trying to solve the mystery of the sum that was coming incorrect in one hundredth part of number

    Instead of 886.32 I get 886,31 and therefore it comes in red at the bottom of the worksheet.

    Thanks very much in advance for any suggestion!
    Attached Files Attached Files
    Last edited by Anchousik; 09-26-2019 at 05:53 AM.

  2. #2
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Currency in decimals and the sum is wrong, complex formula

    I don't see a "sum" formula anywhere. Moreover, the posted formula (in column G) causes a circular reference warning. Presumably, G3:H9 is in a different worksheet (workbook?), which you did not include. But for demonstration purpose, you should explicitly round as follows:

    =IF(F12<>"",IFERROR(ROUND(G12/VLOOKUP(F12,$G$3:$H$9,2,FALSE), 2),"Input XR"),"")

    and

    =ROUND(SUM(G2:G17), 2)

    Even though the first round ensures that each value has only 2 decimal places, the second round is prudent in order to avoid anomalies of binary floating-point arithmetic.
    Last edited by joeu2004; 09-26-2019 at 11:11 AM.

  3. #3
    Registered User
    Join Date
    09-25-2019
    Location
    London
    MS-Off Ver
    16.27
    Posts
    2

    Re: Currency in decimals and the sum is wrong, complex formula

    Joeu2004, you are a genius ! it worked!!!

    thank you very much.

    P.S. yes, there were 2 more worksheets in there that I did not include but the right placing of the Round function made a miracle.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. My if statement with currency keeps coming up wrong please help...
    By jam320 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-05-2017, 03:05 PM
  2. Replies: 2
    Last Post: 02-13-2015, 07:36 AM
  3. Wrong Currency Label on Chart
    By Aland2929 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-17-2013, 10:01 AM
  4. Currency 2 decimals and SUM is wrong!
    By Rober in forum Excel General
    Replies: 5
    Last Post: 01-05-2011, 04:46 AM
  5. Decimals and currency
    By george.bullis@o in forum Excel General
    Replies: 1
    Last Post: 05-14-2007, 05:48 PM
  6. Easy calculating ending with wrong decimals
    By Ronny Andersen in forum Excel General
    Replies: 2
    Last Post: 03-09-2006, 07:29 AM
  7. variant and currency decimals
    By Mary in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-03-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1