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? 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
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, privacy policy and cookie policy

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