Alternative to FLOOR and CEILING functions
Hi! I'm looking for an alternative for the FLOOR and CEILING functions that I use in Excel.
For context, I purchase paper, and buy it in skids and fill the remaining balance in smaller packages. In Excel, I use a formula that references a lookup table to tell me how many sheets of paper come in a skid and how many come in packages. I then use the below formula to tell me how much I need to order.
=IF(C19=0,CEILING(C4,C18),IF(C18=0,0,FLOOR(C4,C18)))+CEILING((C4-D18),C19)
where C4 is the exact number of sheets required, C18 is the number that comes in a skid, and C19 is the number that comes in a package. For example, if I was requested to buy an odd number of sheets, say 1241, the formula tells me that I need to buy 1250 if skids have 1000 sheets and packages have 125 sheets.
SmartSheet does not currently offer the FLOOR and CEILING functions, but I was hoping somebody might have an idea about an alternative for this.
Thanks!
Comments
-
Hello,
Smartsheet currently doesn't have FLOOR and CEILING functions such as excel. When you have a moment, please submit a Product Enhancement Request using the form under Quick links on the right of the community site and let our Product team know that you'd like to see this added to Smartsheet.
I'm not exactly sure how helpful this is, but I do know the math behind rounding to multiples:
roundToMultiple(x,m) = round(x/m)*m
In Smartsheet, you could express this with a function like:
=ROUND([Column Name]1 / 10) *10
The above function will round a number in [Column Name]1 by a multiple of 10
The caveat is that it's rounding; it's not going to take the FLOOR or CEILING of a number.
I haven't figured out how to take floor or ceiling mathematically in Smartsheet yet! If I find a way, I'll comment with more info.
-
My solution for a CEILING function is below:
=IF(ROUND(A1) >= A1, ROUND(A1), ROUND(A1) + 1)
The formula would go in, say, column "B", row 1.
Its input would be the number in column "A", row 1.
The FLOOR function would be:
=IF(ROUND(A1)<=A1, ROUND(A1), ROUND(A1) - 1)
-
This thread was linked to from here...
https://community.smartsheet.com/discussion/rounding-floor#comment-163166
where other options were presented.
Help Article Resources
Categories
Check out the Formula Handbook template!
