# Currency in decimals and the sum is wrong, complex formula

1. ## Currency in decimals and the sum is wrong, complex formula

Hi guys,

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!  Register To Reply

2. ## 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.  Register To Reply

3. ## 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.  Register To Reply

##### Users Browsing this Thread

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

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