2

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

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

1 Answer 1

3

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 |    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.