sum whole numbers

johndem33

New Member
Joined
Apr 20, 2010
Messages
11
I've created a spreadsheet for ordering product. My staff inputs the current invenorty such as 1.25 cases of item A. The spreadsheet has the desired quantity of item A, substracts the current inventory and rounds up as I can only order whole cases. Each item is a row in the spreadsheet and I have over a hundred rows. At the bottom of the spreadsheet, I sum up the rounded counts (the number of cases to order) and I can see a whole nuber such as 53 as a result. The problem is the actual case count to order this week is 61. I think my sum total is off because the cells that are being summed show whole numbers because of the Excel roundup function I use, but in actuallity behind the scene, they contain decimal components too. I want the total cases ordered so I can compare with what the supplier count ordered is. I would appreciate any suggestions to achieve a more accurate total case count.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
It might be useful to see an example of your raw data. It's not clear what you want to achieve.
 
Upvote 0
In cell B7, my staff enters the current inventory. In C7, I've entered the on-going desired inventory. In D7, I added the point to roundup for that item. This provided some flexibility as some items are not used very often, so it is not essential to order them frequently, even when the current inventory is low. Lastly, in E7 is my roundup equation.

So, B7 actually contains the following: 3.00
C7 contains: 3.00
D7 contains: =IF(+C7*(1+$D$134)-$B7<0,0,+C7*(1+$D$134)-$B7)
E7 contains: =IF(D7>1,ROUNDUP((D7-0.09),0),IF(D7>0.09,ROUNDUP(+D7,0),0))

Each row represents an inventoried item and I have such items in rows 5 through 129. At the bottom of column E, I have the following: =SUM(E5:E129)

Unfortunately, the E130 total is incorrect. Today, it shows 61.00, but the total as I manually add up the cases to order in column E is 53. I hope this helps explain the detail you were looking for.
 
Upvote 0
I guess you could say what's in $D$134 and also what is the significance of using .09 with the IFs.
 
Upvote 0
$D$137 is the extended cost of all the items to be ordered (the sum of column F). The .09 is used to roundup from in the 'if' statement. In other words, I don't want to order three cases of an item if the amount being compared is 2.05; I just want to order 2 cases. Where 2.10 and higher of an item will cause me to roundup to 3.

I hope that helps.

Thank you for trying to assist me.
 
Upvote 0
It seems odd to me that you are using a price, even before you determined a qty. I'd think the formula in Cell D7 would come first - figure out if the case qty will be "upped" or not. Then the extended cost is a simple Qty * Price.

Maybe you could give an example of one or two actual calculations -whats in B7, C7, D7, E7, and D134, and maybe again for row 8 and 9 just for kicks. I don't really see why these formulas are set up as they are.
 
Upvote 0
Hmmm, just looking again and now also wondering why extended cost is part of the calculation of the roundup for case quantities - I was thinking it was price. Either way, not sure what's going on. First, determine if a rounding is needed, then, (if needed) roundup/rounddown to a whole case, then calculate extended cost. The requested quantity and the actual ordered quantity are best given columns of their own so you can see the difference - it really should be easy to verify these results.

ξ
 
Upvote 0
Please accept my apologies as I incorrectly answered the question of what is in $D$134. I wrote that it was the extended cost of all items being ordered. I actually wrote the wrong cell too, referring to it as $D$137. That cell is not even used. The extended cost of items being ordered is found in D131 and as you wrote, it has nothing to do with calculating how many cases to order. Sorry.

$D$134 contains '30%' and I use it to adjust how much additional base inventory is needed for contingency. With this global value, I can adjust it in one cell to affect a change for all items instead of having to adjust it in each item's calculation at the row level.

My 6:48 pm post on July 25th describes what is in the cells of B7, C7, D7, and E7. I have gone back and re-verified that I listed this information correctly. And, now you know that D134 contains '30%'.

Thanks for your patience and assistance.
 
Upvote 0
If you are totaling the values in E (the rounded values you have) they are what they are. Once you use the ROUNDUP function, Excel has only the whole number in that cell. It's *not* like with formatting where Excel shows one thing and uses another for calculations.

So, if you have a discrepancy, either you are not totaling E or there is an error in how you got the value of 61.
I've created a spreadsheet for ordering product. My staff inputs the current invenorty such as 1.25 cases of item A. The spreadsheet has the desired quantity of item A, substracts the current inventory and rounds up as I can only order whole cases. Each item is a row in the spreadsheet and I have over a hundred rows. At the bottom of the spreadsheet, I sum up the rounded counts (the number of cases to order) and I can see a whole nuber such as 53 as a result. The problem is the actual case count to order this week is 61. I think my sum total is off because the cells that are being summed show whole numbers because of the Excel roundup function I use, but in actuallity behind the scene, they contain decimal components too. I want the total cases ordered so I can compare with what the supplier count ordered is. I would appreciate any suggestions to achieve a more accurate total case count.
 
Upvote 0
Okay, this looks odd.
E7 contains: =IF(D7>1,ROUNDUP((D7-0.09),0),IF(D7>0.09,ROUNDUP(+D7,0),0))

Doesn't seem to fit the desired result (problem is that is makes no account of case quantities over 1 - most values of D7 will be over .09, so 3.05 will be "True" but should be "False"):
The .09 is used to roundup from in the 'if' statement. In other words, I don't want to order three cases of an item if the amount being compared is 2.05; I just want to order 2 cases. Where 2.10 and higher of an item will cause me to roundup to 3

Maybe:
=IF(D7-INT(D7)>0.1,INT(D7)+1,INT(D7))

That would give:
2.05 --> 2
2.10 --> 2
2.11 --> 3
... and all values of 3 over 3.10 raised to 4
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,272
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top