Share via

Any formula in roundup to the closest to 1, 5 or 10 automatically

Anonymous
2023-03-21T12:08:41+00:00

I have a series of numbers but there are some I want to roundup to nearest next digit, or to nearest 5 or 10 as shown in column 3 and explained in column 4. I have tried IE : @roundup (A3*B2,-1)+A# or @ceiling(A3*B2,1)A3 like in column 5 & 6 but some of the results is what I want but others are not as this is a fixed formula across the rows. I 'm hoping there is a formula where it can roundup this 3 options (next digit, nearest 5 and 10)

Figures Roundup Using @roundup Using @ceiling
5%
15 15.75 16 Next digit 25.00 16
16 16.8 17 Next digit 276.00 268
45 47.25 50 Nearest 10 805.00 801
48 50.4 55 Nearest 5 2318.00 2316
51 53.55 55 Nearest 5 2631.00 2622
150 157.5 60 Nearest 10 8190.00 8183
Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-03-21T12:49:07+00:00

    Hi Raymond!

    I'm Shakiru an independent advisor and a user like you.

    You can use the following formulas to round up to the nearest next digit, 5, or 10:

    Round up to the nearest next digit: =CEILING(A2,1)

    Round up to the nearest 5: =CEILING(A2/5,1)*5

    Round up to the nearest 10: =CEILING(A2/10,1)*10

    Note that in these formulas, A2 refers to the cell containing the number you want to round up. You can replace A2 with any other cell reference as needed.

    Best Regards, Shakiru

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2023-03-21T17:41:06+00:00

    Hi Raymond S!

    Yes, you can use the formula =CEILING(A2/5,1)*5 to round up any numbers to the nearest 5, and then copy and paste the formula to apply it to other cells in the same column.

    The formula divides the number in A2 by 5, rounds it up to the nearest whole number using the CEILING function with a significance of 1, and then multiplies it by 5 to get the rounded-up value to the nearest multiple of 5.

    You can adjust the formula to round up to other intervals or multiples as needed. Just change the number 5 to the desired interval or multiple in both places where it appears in the formula.

    Best Regards, Shakiru

    0 comments
  3. Anonymous
    2023-03-21T13:27:37+00:00

    Yes, I'm using this. What I meant is that if there's any formula IE : =ceiling(A2/5,10,1) where it will automatically round up any numbers accordingly so as I can apply a copy and paste the formula throughout my numbers.

    0 comments