Nested if formula

[SuzyQPA2]

I have a formula and it works but i'd like to tweak it and can't seem to get it.

Here's the formula.
IF(YEAR(C4)=2017,IF(MONTH(C4)=1,F4*1.5%,IF(MONTH(C4)=2,F4*1.25%,IF(MONTH(C4)=3,F4*1%,IF(MONTH(C4)=4,F4*0.75%,IF(MONTH(C4)=5,F4*0.5%,IF(MONTH(C4)=6,F4*0.25%,0)))))))

Basically the formula says if the year is 2017 and the month is January - June, return the corresponding % increase.
Gripes:

I'd also like it to check another cell (j4)and see if the text says "per-diem" if yes then i want it to return zero, if no then continue with the formula.

If you can shorten the formula be my guest

 

[dreid1011]

So, what should the formula show if the date is July-Dec? 0? Blank? F4 value with no increase?

 

[SuzyQPA2]

I can suppress anything with conditional formatting so 0 or false is fine

 

[dreid1011]

If I am not mistaken, then the formula provided by johnmpl already returns a 0 if the date is after June.

 

[SuzyQPA2]

If I am not mistaken, then the formula provided by johnmpl already returns a 0 if the date is after June.

It should return a zero if after may

 

[dreid1011]

It should return a zero if after may

Your original request indicated June as the last month.

 

[SuzyQPA2]

Your right, i'm working with two groups and got them confused, but i was able to tweak your formula to get it to work, what if your formula is june-dec how do i get 0 answer

 

[dreid1011]

Your right, i'm working with two groups and got them confused, but i was able to tweak your formula to get it to work, what if your formula is june-dec how do i get 0 answer

I was just thinking of that because I couldn't think of how to modify the MAX portion, it works so well as it is with 6 numbers... but I modified mine to account for the months after May.

=IF(AND(YEAR(C4)=2017,J4<>"per-diem"),F4*LOOKUP(MONTH(C4),{1,2,3,4,5,6},{0.015,0.0125,0.01,0.0075,0.005,0}),0)

The last number in the LOOKUP ".0025" should be a 0 as shown here in this version.

 

[SuzyQPA2]

Thanks, that worked!

 

[dreid1011]

You're welcome.

 

[mrhstn]

You could skip the AND if returning 0 is acceptable

=(YEAR(C4)=2017)*(J4<>"per-diem")*F4*LOOKUP(MONTH(C4),{1,2,3,4,5,6},{0.015,0.0125,0.01,0.0075,0.005,0})