rounding error

winde

New Member
Joined
Nov 27, 2018
Messages
32
Hi, i'm facing an issue with rounding error and i would like to know if there is a way to solve it.
below is an simple example.

quantityabcd
5035%21%16%28%
using int formula171081449
using round formula181181450

<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col span="3" style="text-align: center;"></colgroup><tbody>
</tbody>

but technically, 18+11+8+14=51

so how can i get exact 50 quantity?

Thanks.
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
Round just does that, up or down by the result, finacial rounding is even more erractic. The results you are getting are what really happens. Just looking at the values 18 is half of 36 and 11 half of 22
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
There is no way to solve what you have there. The only way for the numbers to total the 50 is if you dont round them. Rounding can produce totals that differ slightly than the original total. You could format them to look like integers whilst retaining their underlying value. All depends what you are using it for.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,437
Messages
5,528,761
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top