LinkBack URL
About LinkBacks
Let me see if I can explain this correctly.
I have 3 columns
Column A - weight of items in pounds
Column B - weight of items in adjacent cell in kilograms. This weight is figured by using a product formula multiplying the weight in pounds by .4535924. The cells are formatted to show the result to 3 decimal places. This becomes the official weight for that item in kilograms.
Column C - is the date that item is received
On a separate worksheet, I list the days of the month for each month. In the adjacent cell, I want to sum the total weight in kilograms received on that day. I accomplished that by using a SUMIF formula by date. But this is where my problem comes in.
When excel sums the items received for any particular day, it adds up the entire number to all decimal places and not just the 3 decimal places as shown in the cells, even though the target cell is also formatted to show just 3 decimal places,
for example: 2 items were received on 24 Mar. The weights in kilograms as shown in the cells are 722.573 (rounded from 722.5726932) and 689.468 (rounded from 689.4677054). These are rounded numbers of course and these are now my official weights in kilograms. Add these two numbers and you get 1412.041.
However: when the sumif formula adds these two numbers I get 1412.040. I realize that this is because a summed number may not round exactly as the sum of the individually summed rounded numbers (if that makes sense). But, I need them to.
So: what I need is to find a way to make the cells sum as show, that is 722.573 + 689.468= 1412.041 and not 722.5726932 + 689.4677054 summed and rounded to 1412.040.
If anyone out there knows what I mean, I could use some help getting this fixed or my numbers by the end of the year will be off quite a bit. Thank you.
Register To Reply
Bookmarks