How to use a range in an If statement

1. How to use a range in an If statement

I am charting when events happen. The time they happen is recorded by the hour it occurred in. For example, if the event happened at 12:30am than the time reference is 0, if it happens at 1:30am the time reference is 1, finishing with a 11:30pm event would get a 23 reference. I want to record the event times in four time periods (AM1 = 0 TO 5, AM2 = 6 TO 11, PM1 = 12 TO 17, AND PM2 = 18 TO 23). Here is the statement I want to make in words:

If cell A1 is greater than or equal to zero and less than or equal to five, then enter AM1 in cell B2, or if A1 is greater than or equal to 6 and less than or equal to 11, then enter AM2 in cell B2, A1 is greater than or equal to 12 and less than or equal to 17, then enter PM1 in cell B2, otherwise enter PM2 in B2.

 A1 B2 2 AM1 7 AM2 15 PM1 20 PM2

2. Re: How to use a range in an If statement

This perhaps?

=LOOKUP(A1,{0,6,12,18,24},{"AM1","AM2","PM1","PM2",""})

3. Re: How to use a range in an If statement

Rather than a multiple IF statement, you could use that table (assume it is in X1 to Y4) with a VLOOKUP function, like this:

=IF(A1<\$X\$1,"",VLOOKUP(A1,\$X\$1:\$Y\$4,2))

Hope this helps.

Pete

4. Re: How to use a range in an If statement

Pete - that table is the results, not the lookup table, but yes, a lookup table would be the other way to go.

5. Re: How to use a range in an If statement

I didn't look at the table very closely.

Pete

6. Re: How to use a range in an If statement

It nearly caught me out - I'd created a VLOOKUP based on it before spotting the criteria in the line above!!!

7. Re: How to use a range in an If statement

Thank you, that worked perfectly.

8. Re: How to use a range in an If statement

You're welcome!

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1