How to round value to a multiple of an integer in Google Sheets?

How to make a specific cell (B2) from all sheets to automatically rounddown the input number to the nearest multiple of 4 integer?

Example. When I input a number in cell B2 (33.33), it need to be rounded down to the nearest multiple of 4 integer (32).

If possible, i want that code works for all sheets. What script codes should I use?

13 thoughts on “How to round value to a multiple of an integer in Google Sheets?”

  1. 2021 update:

    Google Docs provides CEILING, FLOOR, and MROUND, which will each round to the nearest multiple of the 2nd argument

    =FLOOR(33.33, 4)
    

    becomes 32

    Original Answer

    Divide the initial value by 4, round that down, and then multiply it by 4 again

    As a "normal" formula:

    =ROUNDDOWN(B2/4)*4
    

    As a custom function

    function Floor4() {
      var spreadsheet = SpreadsheetApp.getActive();
      var initial = spreadsheet.getRange('B2').getValue();
      spreadsheet.getRange('B2').setValue(Math.floor(initial/4)*4);
    };
    
    Reply

Leave a Comment