Nested IF Statements

[TheAssetMgr]

Searching on this topic brings a lot of results and the ones I've dug through always seem to have something about them that's just far enough away from what I'm doing to not help out. That, and jet lag from a round the world trip yesterday is kicking my butt. Would appreciate some assistance!

My goal is to have a cell calculate the number of comp days a team member gets by traveling x number of days. The rules are:

3-6 days of travel results in 1 comp day
7-10 travel days results in 2 comp days
11-14 days gets 3
15-21 days gets 4

Travel of less than 3 days gets no comp days.

I had something working earlier but I've tried modifying it to the point that I can't get back to where it was working. I'd be embarrassed to post what I have at this point and it would probably be easier to start fresh rather than mod what I have. I started with: =IF(AND(E4>2,E4<7),"1"...

There's a whole number (number of travel days) in cell E4 and cell F4 needs to contain the calculated number of comp days.

Any help would be great - thanks!

 

[JackDanIce]

I think Eric may have mis-read the tiers, but taking his suggestion on board, does this work:

=LOOKUP(A1,{0,3,7,11,15,22},{0,1,2,3,4,7})

I'd still advocate Eric's over mine, other people's impressions aren't purpose of the work or output of the sheet.

You can find lots of examples online about LOOKUP, but essentially it's just like VLOOKUP but the search item, search range and return range are all within the formula itself (rather than range addresses, which you can also use but you'd be better off reading and following examples than my overview with caveats and likely mis-understanding!).

In this case, it's matching A1 to the nearest less than match in the array {0, 3, 7, 11, 15, 22} (i.e. 3-6 inclusive are mapped to 3 which is the 2nd position in array) and returning that position from the output array, in this case 1 is in 2nd position of output array: {0, 1, 2, 3, 7}

Therefore anything 22 or above is mapped to 7.

 

[TheAssetMgr]

The updated code didn't work as expected - it added two days to make the total comp days 7 instead of 5. It calculated the same number of travel days as what Eric did (29 days in the example I'm working on) from there things went awry.

No need to burn any more time on it though; I have a working formula now so I'm all set. Cheers!

 

[JackDanIce]

No probs, glad it's resolved!