- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel question
Posted on 3/26/15 at 1:10 pm
Posted on 3/26/15 at 1:10 pm
Is anyone aware of any function that would allow you to round a list of numbers while holding the sum of the rounded amounts equal to the original sum of the non-rounded amounts?
4
Posted on 3/26/15 at 1:30 pm to Big Scrub TX
Just change the formatting to include no decimals?
Posted on 3/26/15 at 1:39 pm to southernelite
quote:
Just change the formatting to include no decimals?
Doesn't work for large amounts.
Posted on 3/26/15 at 1:41 pm to Big Scrub TX
So you are saying you want like 95789.34 to round to 96000 but you want everything to add up with the originals?
Posted on 3/26/15 at 1:51 pm to Big Scrub TX
Sure, plus or minus 1 
Posted on 3/26/15 at 2:02 pm to GenesChin
quote:
So you are saying you want like 95789.34 to round to 96000 but you want everything to add up with the originals?
Yes.
Posted on 3/26/15 at 2:19 pm to Big Scrub TX
If that is the case, easiest thing would probably be having a hidden tab that does calculations on original then on actual page rounding them.
If you want to get extremely technical, create a table that maps rounded numbers to original, do a vlookup to the table for each calculation.
Anyone who tried working on that workbook would hate you for life though
If you want to get extremely technical, create a table that maps rounded numbers to original, do a vlookup to the table for each calculation.
Anyone who tried working on that workbook would hate you for life though
Posted on 3/26/15 at 2:22 pm to Big Scrub TX
=ROUND(NUMBER, DIGITS) doesn't work?
Posted on 3/26/15 at 2:45 pm to torrey225
quote:
=ROUND(NUMBER, DIGITS) doesn't work?
That works for rounding an individual number but it does nothing to hold the entire series to the original sum.
Posted on 3/26/15 at 2:46 pm to GenesChin
quote:
If that is the case, easiest thing would probably be having a hidden tab that does calculations on original then on actual page rounding them.
If you want to get extremely technical, create a table that maps rounded numbers to original, do a vlookup to the table for each calculation.
Anyone who tried working on that workbook would hate you for life though
I'm somewhat surprised that there is no embedded function for this. It seems like a "solver" based type of operation.
Posted on 3/26/15 at 3:25 pm to Big Scrub TX
Describe in a little more detail what you want to do. I think I have something but not sure.
Posted on 3/26/15 at 3:27 pm to Big Scrub TX
Why can't you just have 2 columns, one for the exact figures and one for the rounded figures?
Posted on 3/26/15 at 3:46 pm to iAmBatman
quote:
Why can't you just have 2 columns, one for the exact figures and one for the rounded figures?
How does that make the 2 sums equal each other necessarily?
Posted on 3/26/15 at 3:48 pm to Wasp
quote:
Describe in a little more detail what you want to do. I think I have something but not sure.
I have a long list of big numbers (millions to tens of millions) which often look like this: "11,253,153.64". The list adds up to one sum (X). In order to make the numbers usable, I need to be able to round, potentially all the way to the 10-thousandths place. But I ALSO need the sum of the list of rounded numbers to be exactly equal to X.
Posted on 3/26/15 at 4:11 pm to Big Scrub TX
What if the sum of decimal places does not add to one?
Posted on 3/26/15 at 4:12 pm to Wasp
Sounds a little like office space. Just shave them off, put them in an account.
Posted on 3/26/15 at 4:16 pm to Wasp
quote:
What if the sum of decimal places does not add to one?
They always will because each number in the original list is calculated by taking a percentage off of X.
Posted on 3/26/15 at 4:19 pm to Big Scrub TX
Do the % add up to 100?
Posted on 3/26/15 at 4:26 pm to Wasp
I'm still a little confused on why the rounding would not affect the sum. And if it does affect the sum, how in the world would any calculation you use based on that not screw up
Posted on 3/26/15 at 4:29 pm to Wasp
quote:
Do the % add up to 100?
Yes.
Page 1 of 2
Back to top
Follow TigerDroppings for LSU Football News