+ Reply to Thread
Results 1 to 5 of 5

Help with rounding

  1. #1
    Registered User
    Join Date
    01-25-2013
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Help with rounding

    All looks well in the spreadsheet, but when I bring the data into a mail merge in Word, my numbers are displaying with 17-19 decimal places. I'm having trouble figuring out how to properly round this so it only displays to 2 decimal places when brought into a mail merge.

    With the cell formatted to General - the result displays as .6
    When I copy and paste special values into a new cell the value is .6
    When I pull the field into a mail merge the value is 0.59999999999999998

    =ROUND((VLOOKUP(V2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(W2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(X2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(Y2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(Z2,Sheet3!$A$7:$B$11,2,0))/5,2)

    I've tried a few different variations moving the () around with no luck.

    Any help is appreciated.

    Thanks

  2. #2
    Valued Forum Contributor
    Join Date
    03-29-2013
    Location
    United Kingdom
    MS-Off Ver
    Office/Excel 2013
    Posts
    1,749

    Re: Help with rounding

    Try MROUND(YourFunction,0.1)
    Elegant Simplicity............. Not Always

  3. #3
    Registered User
    Join Date
    01-25-2013
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help with rounding

    Thanks for the reply.

    I changed it to =MROUND((VLOOKUP(V2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(W2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(X2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(Y2,Sheet3!$A$7:$B$11,2,0)+VLOOKUP(Z2,Sheet3!$A$7:$B$11,2,0))/5,0.1)

    That produces 0.60000000000000009 in the mail merge

  4. #4
    Forum Contributor
    Join Date
    04-23-2013
    Location
    Abu Dhabi United Arab Emirates
    MS-Off Ver
    Excel 2010
    Posts
    178

    Re: Help with rounding

    you need to format your mail merge field to 2 decimal point:
    { MERGEFIELD “Fielname” \# ,#.00 }

  5. #5
    Registered User
    Join Date
    01-25-2013
    Location
    PA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Help with rounding

    Wow - never knew that existed. I thought the formatting had to be done in the data source. Many thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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