I have a table of cells with integer numbers (see this file and the table called ExampleTable1).

I want to convert the table to table with integers, which are rounded up to the smallest number, bigger then the input and divisible by 50 (see ExampleTable3).

For example I have number 123. I want to be converted to 150, since it is the smallest integer bigger than 123 and divisible by 50.

Here is, however, the tricky part. I can easily use =roundup(123/50;0)*50 and It will return 150 (see ExampleTable2).

Why don't I use roundup in a copy of my table?

  (1) The actual table that I want to "roundup" is huge with some texts and formatting so I don't want to change them inadvertently.

  (2) There are some formulas, which go very incorrect, if I round their cells up. See NoNoNo row.

  (3) I will gladly learn something new, if there is a way to solve my problem the way I describe it.

  (4) I am too lazy to write the roundup formula and drag it all over the copy table, because it is hundreds of rows and columns.

Is there a way to use for example the Right Click -> Format Cells -> The "Custom" category and make it to automatically roundup the values the way I ask here?

Is there a way to do it in other fast way, like a macro or something? I am really very pretty bad at Visual Basic, so please be sure, that if you are gonna give me a VBA solution, I will ask you some irritating stuff back, for the fact that I have probably not gotten it correctly.

Thank you all in advance.

  • For point (4), see this – cybernetic.nomad Oct 1 at 14:24
  • Looking at the guidelines for customizing formats, I don't think it is possible to always round up or perform calculations. – Stormweaker Oct 2 at 9:50
  • Well, thank you very much @cybernetic.nomad. I wasn't aware of this feature, but it doesn't actually get to solve my issue. It seems nobody is interested in it, so let's just forget it. – ecclesiastes Oct 5 at 13:13
  • Thank you @Stormweaker, it looks like my issue will not be solved that easy and I will have to do it manually. – ecclesiastes Oct 5 at 13:15

Your Answer

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

Browse other questions tagged or ask your own question.