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.
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
6,996
It might be useful to see an example of your raw data. It's not clear what you want to achieve.
 

johndem33

New Member
Joined
Apr 20, 2010
Messages
11
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
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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
Joined
Apr 20, 2010
Messages
11
$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
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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
Joined
Apr 20, 2010
Messages
11
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
Joined
May 28, 2002
Messages
11,007
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
Joined
Mar 2, 2007
Messages
16,553
Office Version
2013
Platform
Windows
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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top