I'm trying to format a google sheet to calculate the amount of raw lumber I need to purchase for a given project.

Raw lumber is sold in quarters, so for example 4/4 is a one inch board, 5/4 is an inch and a quarter, and so on.

I've managed to format my cell to always output a number over 4, so if I input 1 1/8 final dimension it will give me 6/4 in raw lumber (final dimension + 1/4"). but the problem I run into is that there are certain sizes that lumber isn't sold in, like 7/4, 9/4 and 11/4.

Is it possible to force a cell to round up to a given list of numbers: 4/4, 5/4, 6/4, 8/4, 10/4, and 12/4

Here is my sheet:


If you set the number in cyan (thickness final dimensions) to 2, the number in yellow (thickness raw lumber) goes to 9/4, but that isn't an option I would like it to bump up to 10/4. Same goes for any instance where it would show up as 7/4 or 11/4.

  • Fred, yes, this is possible. But I/we would need to see your sheet (or a copy of it) in order to write formulas or talk to you in a meaningful way about how and where to set things up. If you'd like, use File > Share to create a link, being sure to select "Anyone with the link can Edit" (not just "...can View") and share that link here.
    – Erik Tyler
    Dec 8 '18 at 15:05
  • Hi thanks Erik I've edited my post to add the link.
    – fred
    Dec 9 '18 at 16:30
  • Thanks for sharing the sheet. In taking a look, and even with your description thus far, it's not clear to me what the sheet overall is supposed to do, and which cells would be doing calculations that you want rounded up. Feel free to jot notes on the sheet itself, and then comment back here. I'll take another look then.
    – Erik Tyler
    Dec 10 '18 at 18:43
  • In my sheet the column highlighted in cyan is my known variable, we'll call it C, here I input any number between 0 and 2.75. The column in yellow, let's call it Y, should output C + 0.25". So Y = C + 0.25", but Y can only be expressed as number over 4 and can only be one of the following options 4/4, 5/4, 6/4, 8/4, 10/4, 12/4, any result in between should round up to the closest option listed.
    – fred
    Dec 13 '18 at 13:34

OK, Fred. Thanks for the full explanation with notes. Very helpful.

There are a several ways to go about this. But since you have such a limited set, this is what I went with (implemented in your test sheet for you):


IFS is a compact alternative to nested IF statements. I used this since you only have three exceptions.

The formula, in plain English: "IF the new number is 7/4, make it 8/4; IF the new number is 9/4, make it 10/4; if the new number is 11/4, make it 12/4; otherwise (i.e., TRUE), just return the number as it was because it is already a viable measurement."


In case anyone else comes looking for a more robust answer:

CEILING(A1 / (1/4), 1) * (1/4)

What this does in English: If cell A1 has the value we want to round up:

  1. Divide the value in A1 into quarters so we get the numbers of quarters.
  2. Perform the ceiling operation, which rounds up to the nearest whole number of quarters. If we had 6.125 quarters this would result in 7 quarters.
  3. Multiply the number of quarters by 1/4. E.g. 7 quarters = 7 * 0.25 = 1.75"

You could use any fraction you like and even put it in a cell and use a reference.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.