Google-sheets – Division in Google Spreadsheets

google sheets

How do I accomplish the following:

I have a cell C5 with various numerical values.
If C5 is less than 30 return 0 in C6
If C5 is greater than 30 return 1hr in C6
if C5 is greater than 60 return 2hr in C6
if C5 is greater than 90 return 3hr in C6 etc..

Best Answer

If we assume that a value of 30 should return 0, then the following formula could be entered into cell C6:

=CEILING(C5/30) - 1

It divides the value found in C5 by 30, rounds it up to the nearest integer (the CEILING function), and subtracts 1. This table shows the result of a few selected input values:

        Column C
Row 5 => | 29 | 30 | 31 | ... | 59 | 60 | 61 | ... | 89 | 90 | 91 |    
Row 6 => |  0 |  0 |  1 | ... |  1 |  1 |  2 | ... |  2 |  2 |  3 |

If instead a value of 30 should return 1, use

=FLOOR(C5/30)

which divides the input value by 30, and rounds it down to the nearest integer (the FLOOR function).

It will result in the following values:

        Column C
Row 5 => | 29 | 30 | 31 | ... | 59 | 60 | 61 | ... | 89 | 90 | 91 |    
Row 6 => |  0 |  1 |  1 | ... |  1 |  2 |  2 | ... |  2 |  3 |  3 |
Related Topic