Help to round up to full number when not whole number as source
I have a logistic case to make a form for customers to fill out when ordering items.
They can input m2 (sqare-meters) and the form will calculate the amount of pallets and pieces they will need.
Usually this would be simple, but a whole pallet (in this case) is 48,5 pieces (as in, 48 and a half piece) and thus the formula I wanted to use does not work properly. A customer can order the item in pallets and/or whole pieces (so they can't order half a piece)
One full pallet is 58,2 m2 and one piece is 1,2 m2.
This is how it roughly looks:
https://i.imgur.com/XJzVtLW.png
Cell A is the item's m2 size for one pallet. The content of this cell always remains the same.
Cell B is where the customer can input how much m2 they want to order
Cell C is the estimated m2 of the item the customer will get, based on input in Cell B
Cell D is the estimated amount of pallets the customer will get, based on input in Cell B
Cell E is the estimated amount of pieces the customer will get, based on input in Cell B
My current formulas:
Cell C: =(D1*A1)+(E1*1,2)
Cell D: =IF(B1<A1;0;ROUNDDOWN(B1/A1;0))
Cell E: =IF(D1=0;ROUNDUP(B1/1,2;0);ROUNDUP((B1-(D1*A1))/1,2;0))
Generally, this works fine and gives accurate m2, pallets and pieces for Cell C, D and E.
However, when you input a number very close to one or more full pallet sizes (58, 116 etc.) then the results become inaccurate (because amount of pieces for one pallet is not a whole number).
I tried to make a workaround for this:
Cell C: =IF(OR(B1=115;B1=116);116,4;IF(OR(B1=173;B1=174);174,6;IF(B1=232;232,8;(D1*A1)+(E1*1,2))))
Cell E: =IF(OR(B1=115;B1=116;B1=173;B1=174;B1=232);0;ROUNDUP((B1-(D1*A1))/1,2;0))
This workaround actually gives the results I want, but surely there must be a better solution than to write down every single number you want to round up to a full pallet inside the formulas.
Anyone can help me with this? :)
Posted Feb 20, 2019, 1:33 PM UTC
=MROUND(CELLREFERENCE,58,2)?
Could you provide with an example on how I could fit the MROUND into my formulas in a smart way?
CELLREFERENCE can be the Input you get in m2. So, the calculation gives you the following: https://ibb.co/QFzs3JJ
The formula removes the need for accounting for different sizes of pallets in m2.
GL
But I dont want to always round up to the pallet size, since the product can be ordered in both pallets and/or pieces. It's just when they input a m2 size that could/should be rounded up to a pallet size because of that annoying half piece extra the full pallet has (input 58 in A1 makes B1 become 58,8 with old formula and 58,2 with MROUND) but if I put in 40 then it will give me 40,8 m2 (because that equals 34 pieces) with old formula, but 58,2 with MROUND.
You understand where I want to go with this?
Hmm..I think I see that but the problem with excel is that It cannot differentiate whether the user input is in m2 or in pieces.
What you can do is to have an option for the user to input the data in a structured way. If they enter data in m2, then use mround(CR,58.2); if they enter in pieces, then use mround(CR, 34?).
You can actually implement this easily. In the cell right next to input cell, get a small data validation, with option m2 and Pieces. After the user enters the input, he/she can then select the unit of measure. Based on the inputs:
if(m2,mround(58.2),mround(34?))