Refine formula

[tallgeese980]

Hi all i have created a formula that i believe can be refined and shorted because it has become a huge mess. The formula below is used to pull data based on a specific date.

=IF(BS$7>=$D$9,IF(AND(($D$9<="12/31/2012"+0),($D$9>"12/31/2011"+0)),'Input Data'!$H$85*HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0),IF(AND(($D$9<="12/31/2013"+0),($D$9>"12/31/2012"+0)),'Input Data'!$H$91*HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0),IF(AND(($D$9<="12/31/2014"+0),($D$9>"12/31/2013"+0)),'Input Data'!$H$97*HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0),IF(AND(($D$9<="12/31/2015"+0),($D$9>"12/31/2014"+0)),'Input Data'!$H$103*HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0),IF(AND(($D$9<="12/31/2016"+0),($D$9>"12/31/2015"+0)),'Input Data'!$H$109*HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0),IF(AND(($D$9<="12/31/2017"+0),($D$9>"12/31/2016"+0)),'Input Data'!$H$109*HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0),0)))))),0)

 

[RSXchin]

you could... make a custom function I suppose... But that's a beast of a formula

 

[tallgeese980]

Yeah I agree and tried but was unable to get it to work correctly. Also, the formual has an issue with the dates when pulling the data. Its pulling numbers for a depreciation schedule on multiple projects with diferent start dates but all having 4 year depreciation lives. However, it ends up pulling depreciation for a full year (the last year of the derprecation life) even though the project started on the 2/11/11. So for 2015 it should only charge up till feb but it pulls data in for every month that year.

 

[mvptomlinson]

Two quick recommendations:

1. Replace all instances of: AND(($D$9<="12/31/yyyy"+0),($D$9>"12/31/yyyy"+0))

With: YEAR($D$9)=yyyy

2. Put the formula =HLOOKUP('Input Data'!$C$3,'Allocation %s'!$C$3:$L$23,5,0)

in some empty cell. Then replace all instances of that formula with the reference to that cell. Assuming that cell is G1:

=IF(BS$7>=$D$9,IF(YEAR($D$9)=2012,'Input Data'!$H$85*$G$1,IF(YEAR($D$9)=2013,'Input Data'!$H$91*$G$1,IF(YEAR($D$9)=2014,'Input Data'!$H$97*$G$1,IF(YEAR($D$9)=2015,'Input Data'!$H$103*$G$1,IF(YEAR($D$9)=2016,'Input Data'!$H$109*$G$1,IF(YEAR($D$9)=2017,'Input Data'!$H$109*$G$1,0)))))),0)

Hopefully that works. I'm sure you could even shorten it more since there appears to be a pattern (Hxxx changes by six rows for each year....) I also noticed that H109 is used as the multiplier for both 2016 and 2017. Either a typo, or it's meant to be that way based on your data.

 

[mvptomlinson]

Possible shorter solution:

=IF(BS$7>=$D$9,INDIRECT("$H$"&LOOKUP(YEAR($D$9),{2012,2013,2014,2015,2016,2017},{85,91,97,103,109,115}))*$G$1)

 

[tallgeese980]

That is a brilliant suggestion thank you!

Still unable to get it to stop the depreciation on the month however.

 

[mvptomlinson]

I'm out of my league on depreciation, unfortunately. I hate all things accounting and finance related.

 

[tallgeese980]

I would think that perhaps selecting singling out the month would work but unfortunately then it excludes the year too.