Simple Logical Formula

[austinandrei]

Hi,
I'm having a hard time in trying to simplify my IF formula without restating the logic if the value was not achieve.
Say I'm trying to get the difference between years then add one and if the answer is 5, then it will take 4 instead of 5 so I'm using below:

=IF(1+YEAR(E$5)-YEAR($I$5)>4,4,1+YEAR(E$5)-YEAR($I$5))

What I don't want or to eliminate in the IF formula is the redundant statement of the RED font. Is it possible that if the answer is <=4, then just use the answer on the BLUE font and not to reinstate it again as it is making my overall formula longer.
Thanks!

 

[shg]

=min(4, year(e5) - year(i5) + 1)

 

[austinandrei]

Thanks a lot shg! Didn't thought of using MIN formula for that.
But for future reference as the sample above will not always be a number, would there be a simple IF?

 

[shg]

as the sample above will not always be a number

For example, ...?

 

[austinandrei]

Hi shg,I'm okay now. I just thought that IFERROR will do for the samples I have which is not applicable to the above sample. Thanks!

 

[austinandrei]

Hi, I just thought of one which cannot be done using IFERROR or MIN
=IF(1+YEAR(E$5)-YEAR($I$5)>4,"More than 4Yr Plan",1+YEAR(E$5)-YEAR($I$5))

 

[austinandrei]

I have same issue as above now since E5 might be blank so it would pick up Year(E$5) as 1901 since Cell E5 is blank. so the answer would be -116 and Min(4,-116) would pick -116 but instead of -116, any number below 1 should pick 1 as the minimum.

Also,should not be an array formula. Thanks.

 

[austinandrei]

Sorry for like spamming the recent list. I know what to do now. using Max(1+YEAR(E$5)-YEAR($I$5),1) inside the MIN formula to force all negative values to 1.
My pending question now is the one above:

=IF(1+YEAR(E$5)-YEAR($I$5)>4,"More than 4Yr Plan",1+YEAR(E$5)-YEAR($I$5))

 

[Rick Rothstein]

Sorry for like spamming the recent list. I know what to do now. using Max(1+YEAR(E$5)-YEAR($I$5),1) inside the MIN formula to force all negative values to 1.
My pending question now is the one above:

=IF(1+YEAR(E$5)-YEAR($I$5)>4,"More than 4Yr Plan",1+YEAR(E$5)-YEAR($I$5))

Here is one way to do it without the repeated calculation...

=IFERROR(0+TEXT(YEAR(E5)-YEAR(I5)+1,"[<5]0;"""""),"More than 4Yr Plan")

Personally, I would just go with the repeated calculation.