2

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.

Improve this question
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
    Commented Dec 8, 2018 at 15:05
  • Hi thanks Erik I've edited my post to add the link.
    – fred
    Commented Dec 9, 2018 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
    Commented Dec 10, 2018 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
    Commented Dec 13, 2018 at 13:34

3 Answers 3

Reset to default
1

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

Improve this answer
2

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.

Improve this answer
2
  • You would need to do that if you used ROUNDUP since it can only handle powers of 10, but with CEILING you can give it 1/4 as the second argument to get the number you want without having to divide and multiply. I've added an answer to show it.
    – David Mason
    Commented May 16, 2023 at 23:56
  • You're right @DavidMason, your answer is the right answer!
    – Gareth Farrington
    Commented Mar 26 at 23:29
1

The CEILING function already does exactly what you need, so there is no need to divide then multiply:

CEILING(A1, 1/4)

CEILING will round the first number up to the nearest multiple of the second number. It doesn't have to be fractions,

e.g. If I had lumber in 2.4m lengths and wanted enough for 7.5m then

  • CEILING(7.5, 2.4) gives the total length (9.6m)
  • CEILING(7.5, 2.4)/2.4 gives the count (4 lengths)

The opposite function is FLOOR if you want to round down to a fraction.

e.g. If I have $123 and I an buy lumber for $9.66/length:

  • FLOOR(123, 9.66) gives the amount I can spend on whole lengths ($115.92)
  • FLOOR(123, 9.66)/9.66 gives the count (12 lengths)
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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