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]

Try:

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

This limits comp days to 4, if they travel 15 days or more (no upper limit) or 0 if a value less than 3 or non-numerical value is supplier.

 

[TheAssetMgr]

Wow, thank you! That works perfectly right out of the gate and I have no idea how! I'll see if I can pick it apart later but for now I'll just revel in having a working formula.

After reading the part of your response where you mentioned 4 was the maximum number of comp days I went back and re-read the policy. Turns out that, after 21 days of travel, the employee gets an additional day of comp time for every 7 days over the initial 21. Any way to mod your formula to account for that little part?

Thanks again, you've already helped tremendously.

 

[JackDanIce]

You're welcome.
Adjust D15 as input cell and try:

=IF(D15>21,ROUNDDOWN((D15-21)/7,0)+4,IFERROR(MIN(LOOKUP(D15,{3,7,11,15},{1,2,3,4}),4),0))

 

[TheAssetMgr]

I stand in awe of your genius my friend. I am not worthy. Thank you!

Once again, your code worked straight out of the box as advertised - I couldn't ask for more. Well, I may think of something later since you obviously know what you're doing. Can you code a macro to get me out of the doghouse with my wife when necessary?

Many thanks!

 

[TheAssetMgr]

Annnd... once again (this time hopefully the last) I've noticed another gotcha in the policy. The max comp days one can earn per trip is 7. I can put some conditional formatting on the comp day field to go red when anything greater than 7 is returned but that doesn't stop the extra days from being added to the field and thus to another part of the spreadsheet.

Any way to cap the value for this field at 7 no matter what the input is?

 

[JackDanIce]

Meh, normally I write some rubbish, don't test it then have bugs to decode, thanks for feedback!

This limits upper value to 7, regardless of days travelled, adjust D15 as before:

=IF(D15>21,MIN(ROUNDDOWN((D15-21)/7,0)+4,7),IFERROR(MIN(LOOKUP(D15,{3,7,11,15},{1,2,3,4}),4),0))

 

[Eric W]

If you cap the maximum days earned at 7, that essentially limits the number of tiers to consider. Jack, you can essentially go back to your original formula and just add those tiers:

=LOOKUP(D15,{0,3,7,11,15,28,35,42},{0,1,2,3,4,5,6,7})

This matches the results from your latest formula.

TheAssetMgr, you may want to double check your formula. For the first 4 tiers, the gap between adding a day is about 4. Then you have a gap of 11, then it drops to 7. It could be correct, but that 11 day gap is suspicious.

 

[JackDanIce]

Nice simplification of original equation Eric thanks, I was just "bolting" on the adjustments than considering how to adjust the lookup and return vectors

 

[TheAssetMgr]

DanJackIce - Your update worked perfectly. Best rubbish coding ever.

Eric W - Your condensed code also worked flawlessly - thanks very much!

I'm torn between using the longer code or the shorter... the longer looks more complicated and may provide an undeserved low whistle if someone else looks at the formula I didn't come up with and thinks it's awesome because it's visually undecipherable to the common man.

The shorter though is more elegant but is also likely to result in a low whistle of admiration at some point in the future. Lol - I don't fully understand either of your work but I'm really glad it's functional.

Thanks again for being willing to help out on this one guys - hope it'll help someone else in the future too.

Eric - I'm not quite sure i'm understanding the gap you're pointing out. If it has to do with the number of travel days that earns x amount of comp time then I'll point at HR and shrug. Otherwise, can you provide detail on what you're seeing in case I've missed something critical?