• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF formula, w/ date lookup and set figures returned

R

rfreeman

New Member
I am trying to put together an analysis sheet, and i need to look up a date column, and if the year equals a pre defined value i need it return a set value.
Example:
Column I contains contract end dates in the format mm/dd/yyyy.
If the date contract ends in 1 of four years, either 2015, 2016, 2017 or 2018 I want to return one of four nominal vlaues.
So if I2 ends in 2015 i want $5000, 2016 = $10000, 2017 = $15000, 2018 = $20000.
I have around 200 rows of data.
 
Hi ,

Try this :

=5000*MIN(4,MAX(0,(YEAR(I2)-2015)+1))

Enter this in any unused column , and copy down as far as your data extends.

This formula will enter 20000 if your end date is beyond 2018 e.g. 2019 , 2020 ,... ; is this acceptable , or should it revert to 0 in these cases ?

Narayan
 
Thanks Narayan, however I was looking at returning the set values for each of the 4 years, as there is no projections beyond 2018
ie
2015= $5000
2016 = $10000
2017 = $15000
2018 = $20000.
 
Hi ,

In that case , just use an IF statement to exclude the years beyond 2018 , as in :

=IF(YEAR(I2)>2018,0,5000*MIN(4,MAX(0,(YEAR(I2)-2015)+1)))

Narayan
 
Hi Narayan,
As there will only be 5 years applicable (2015-2018) it seems overly complex to add the MIN/MAX references.
To be honest i don't really understand what the formula is about, with all the MIN/MAX number calculations
 
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)

Code: 
=MIN(A1: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
 
Hi rfreeman,

You can also try this with your dates in column A

Code: 
=LOOKUP(YEAR(A1),{2000,2015,2016,2017,2018},{0,5,10,15,20}*1000)
 
You must log in or register to reply here.
Back
Top