# sum whole numbers

#### johndem33

##### New Member
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.

#### dave3009

##### Well-known Member
It might be useful to see an example of your raw data. It's not clear what you want to achieve.

#### johndem33

##### New Member
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.

#### xenou

##### MrExcel MVP, Moderator
I guess you could say what's in \$D\$134 and also what is the significance of using .09 with the IFs.

#### johndem33

##### New Member
\$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.

#### xenou

##### MrExcel MVP, Moderator
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.

#### xenou

##### MrExcel MVP, Moderator
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.

ξ

#### johndem33

##### New Member
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.

#### tusharm

##### MrExcel MVP
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.

#### xenou

##### MrExcel MVP, Moderator
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

1,082,284
Messages
5,364,282
Members
400,787
Latest member
bs04c

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...