Hi Freeman ,
I think you should make the effort to understand these , since they add to your Excel armoury.
The MIN function returns the lesser of two , or the least of more than 2 variables passed as parameters to it ; for instance , if you have the following values :
A1 = 5
B1 = 7
and C1 contains the formula =MIN(A1,B1) , then C1 will display 5. If C1 contains the formula =MAX(A1,B1) , it will display 7.
Extending the formula to more than 2 variables is possible either by adding more of them , or by passing a range as a parameter ; for example , if you want the minimum value in the range A1 through D1 , both the following formulae will work :
=MIN(A1,B1,C1,D1)
In your example , consider what the inner-most calculation is doing ; the formula used there is :
(YEAR(I2)-2015)+1
If the values in cell I2 can vary from say 2010 through 2020 , then the results of the above formula for these input values will be :
Code:
1/1/2010 -4
1/1/2011 -3
1/1/2012 -2
1/1/2013 -1
1/1/2014 0
1/1/2015 1
1/1/2016 2
1/1/2017 3
1/1/2018 4
1/1/2019 5
1/1/2020 6
Putting a MAX(0, ....) outside the above formula , as has been done in MAX(0,(YEAR(I2)-2015)+1) basically eliminates the negative values , since =MAX(-3,0) will result in 0. We will now have , for the same input date values , the following :
Code:
1/1/2010 0
1/1/2011 0
1/1/2012 0
1/1/2013 0
1/1/2014 0
1/1/2015 1
1/1/2016 2
1/1/2017 3
1/1/2018 4
1/1/2019 5
1/1/2020 6
Putting a MIN(4, ....) outside the above formula , as has been done in MIN(4,MAX(0,(YEAR(I2)-2015)+1)) basically limits the maximum positive value to 4 , since =MIN(4,6) will result in 4. We will now have , for the same input date values , the following :
Code:
1/1/2010 0
1/1/2011 0
1/1/2012 0
1/1/2013 0
1/1/2014 0
1/1/2015 1
1/1/2016 2
1/1/2017 3
1/1/2018 4
1/1/2019 4
1/1/2020 4
Now , if you want that years beyond 2018 should not result in 4 , but should result in 0 , we use the IF statement to take care of this.
Narayan