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 '15 at 22:31
• If you're satisfied with the answer, you should upvote and/or mark it as accepted. Aug 17 '15 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 |