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.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
sum whole numbers
- Thread starter johndem33
- Start date
-
- Tags
- sum whole numbers
Sorry it has taken me a couple days to respond. I work in the restaurant business and the hours are significant.
I manually ran a few values through my E7 formula and the results come out as desired. So, I'm not sure why you wrote that the formula looks odd. As you saw, the formula consists of two basic parts. The first tests if D7 is > 1. If it is, it rounds D7 minus .09, up to a whole number. The second part is only executed when D7 is equal to or less than 1. Hence, a D7 value of > .09 and is rounded to 1 and all else yields a result of 0.
The problem I am trying to figure out is why does the sum of column E yield a number higher than the displayed values in column E? It's acting like the actual values stored in the E cells displays whole numbers, but when summed up, they carry the decimal components, yielding a larger product. Ugh!
Since my goal is to know how much to order of each item and to have an accurate total count of total cases to order, I went ahead and substituted your formula in place of mine for each item. To my disappointment, the total cases to be ordered by the column, summed to the same number as mine and this number is higher than it should be when compared to adding up manually the displayed number of cases to order. Ugh again! This leads me to believe that my original suspecian is true, the displayed item numbers are not what is summed for the column. Meaning, if the formulas indicate I'm to order 2 cases of an item because the displayed number of cases to order is 2, there is actually something other than 2 being summed for in the column as the column sum result is higher than the sum of the displayed item numbers.
I've also played with formatting the cells in column E as number with and without decimal places as well as I tried formatting the cells as general. I was hopeful to see decimal values when the cells were formatted as number with 2 decimal places, but to my disappointment, the cells only displayed .00 value. This of course burst my theory.
Any other suggestions folks?
I manually ran a few values through my E7 formula and the results come out as desired. So, I'm not sure why you wrote that the formula looks odd. As you saw, the formula consists of two basic parts. The first tests if D7 is > 1. If it is, it rounds D7 minus .09, up to a whole number. The second part is only executed when D7 is equal to or less than 1. Hence, a D7 value of > .09 and is rounded to 1 and all else yields a result of 0.
The problem I am trying to figure out is why does the sum of column E yield a number higher than the displayed values in column E? It's acting like the actual values stored in the E cells displays whole numbers, but when summed up, they carry the decimal components, yielding a larger product. Ugh!
Since my goal is to know how much to order of each item and to have an accurate total count of total cases to order, I went ahead and substituted your formula in place of mine for each item. To my disappointment, the total cases to be ordered by the column, summed to the same number as mine and this number is higher than it should be when compared to adding up manually the displayed number of cases to order. Ugh again! This leads me to believe that my original suspecian is true, the displayed item numbers are not what is summed for the column. Meaning, if the formulas indicate I'm to order 2 cases of an item because the displayed number of cases to order is 2, there is actually something other than 2 being summed for in the column as the column sum result is higher than the sum of the displayed item numbers.
I've also played with formatting the cells in column E as number with and without decimal places as well as I tried formatting the cells as general. I was hopeful to see decimal values when the cells were formatted as number with 2 decimal places, but to my disappointment, the cells only displayed .00 value. This of course burst my theory.
Any other suggestions folks?
Upvote
0
Excel Facts
Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
xenou
MrExcel MVP
- Joined
- Mar 2, 2007
- Messages
- 16,836
- Office Version
- 2019
- Platform
- Windows
It looks odd to me because what if your order amounts are:
3.1
4.95
7.05
2.4
All of the numbers above are > 1, and > .09 Even your example of 2.05 is already "off the charts". Or to put it another way, your formula only works for quantities between 0 and 1 - after that (numbers greater than 1) the "answer" will always be the same no matter what number you put in. I should probably look again to make sure what I'm saying is right - but of course you will test it and see.
How about posting some sample data with your results? Not just one number - three or four. It really should be easy. Here's the numbers, here's my results. Are they "rounding up" correctly or not?
Last edited:
Upvote
0
STOP - Read this first! My sincere apologies to all that have spent time on this post! Here is why: I was writing the information below to xenou when I thought of an embarassing possibility that might be causing my column sum problem. I looked at the row numbers and sure enough, some rows were hidden. I unhid the rows and quickly saw two with values in the to order column. Although hidden, these rows are still summed in the column total. I checked to see why the rows were hidden and found that these items are seasonal and are not to be ordered at this time. I could have made up a big lie, but would rather apologize and realize again that I am human. I am sorry to have wasted other people's time.
In case anyone is interested - Test data proves that the formula I use in column E and the one proposed by xenou do basically the same thing. Again, thank you xenou and the others!
- - - - The following was written before the above. - - - -
xenou, I appreciate you sticking with me on my Excel problem.
Please re-read my 2nd paragraph in today's 6:08 pm post. I have run many numbers through my spreadsheet and formulas and the E7 formula is working as intended.
The following is in response to your suggestion of providing some sample data.
1.) Column B contains current inventory in cases, accurate to 2 decimal places
2.) Column C contains the desired inventory in cases
3.) Column D is used to calculate the delta between each item in column B and C after applying a contingency factor of 30%. Meaning, if the desired case inventory count is 3, it is bumped up to 3.9 (130% of target).
4.) Column E is used to round up cases to be ordered, but don't round up decimals between .01 and .1 for each value in column D. The intent is a value of .05 would yield a 0 quantity order, a value of .10 would yield 1 case to be ordered, a value of 1.1 would yield 2 cases to be ordered, and so.
5.) Column F is used to calculate the entended cost of each item to be ordered as I have the case cost in another column and that is multiplied by the value for each item to be ordered.
6.) A sum calculation is after the last item row to calculate the total number of whole cases to order.
Now, for some sample data:
Row Col B Col C Col D Col E Col F
5 1.10 0.20 0.00 0 $ 0.00
6 0.75 1.00 0.55 1 $47.71
7
In case anyone is interested - Test data proves that the formula I use in column E and the one proposed by xenou do basically the same thing. Again, thank you xenou and the others!
- - - - The following was written before the above. - - - -
xenou, I appreciate you sticking with me on my Excel problem.
Please re-read my 2nd paragraph in today's 6:08 pm post. I have run many numbers through my spreadsheet and formulas and the E7 formula is working as intended.
The following is in response to your suggestion of providing some sample data.
1.) Column B contains current inventory in cases, accurate to 2 decimal places
2.) Column C contains the desired inventory in cases
3.) Column D is used to calculate the delta between each item in column B and C after applying a contingency factor of 30%. Meaning, if the desired case inventory count is 3, it is bumped up to 3.9 (130% of target).
4.) Column E is used to round up cases to be ordered, but don't round up decimals between .01 and .1 for each value in column D. The intent is a value of .05 would yield a 0 quantity order, a value of .10 would yield 1 case to be ordered, a value of 1.1 would yield 2 cases to be ordered, and so.
5.) Column F is used to calculate the entended cost of each item to be ordered as I have the case cost in another column and that is multiplied by the value for each item to be ordered.
6.) A sum calculation is after the last item row to calculate the total number of whole cases to order.
Now, for some sample data:
Row Col B Col C Col D Col E Col F
5 1.10 0.20 0.00 0 $ 0.00
6 0.75 1.00 0.55 1 $47.71
7
Upvote
0
