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

• What if `C5 = 30`? Aug 16, 2015 at 22:31
• If you're satisfied with the answer, you should upvote and/or mark it as accepted. Aug 17, 2015 at 5:30

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 |
``````