Google Sheets – Force Rounding Up to Specific Fractions

google sheets

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:

https://docs.google.com/spreadsheets/d/1iZfVLeJdres8nJSghDfm-ibGlbFqYEoMIdgnY-MnWvQ/edit?usp=sharing

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.

Best Answer

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(F3+1/4=7/4,8/4,F3+1/4=9/4,10/4,F3+1/4=11/4,12/4,TRUE,F3+1/4)

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."

Related Topic