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

 

[Myrko]

=IF(J4="per-diem",0,Your formula)

 

[SuzyQPA2]

It says too many levels of nesting

 

[johnmpl]

Hi, to all!

It says too many levels of nesting

If you have this message, you use Excel 2003 or earlier.

You can use this formulae instead:
=F4*IF((J4<>"per diem")*(YEAR(C4)=2017),MAX(0,1.75%-MONTH(C4)*0.25%))

Blessings!

 

[SuzyQPA2]

Lovely, thanks

 

[dreid1011]

Perhaps something like this:

=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.0025}),0)

 

[dreid1011]

Perhaps something like this:

=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.0025}),0)

And of course, this will provide incorrect results if a date is entered for July-December. So, disregard the formula.

 

[SuzyQPA2]

Hi, to all!



If you have this message, you use Excel 2003 or earlier.

You can use this formulae instead:
=F4*IF((J4<>"per diem")*(YEAR(C4)=2017),MAX(0,1.75%-MONTH(C4)*0.25%))

Blessings!

Thanks - but if July - Dec in 2017 they don't get it - how do i adjust the formula

 

[dreid1011]

What SHOULD they get in July-Dec?

 

[SuzyQPA2]

What SHOULD they get in July-Dec?

None, because they would be too new