Alternative to FLOOR and CEILING functions

ToddE
ToddE ✭✭
edited 12/09/19 in Formulas and 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!

Tags:

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!