Vehicle Replacement Forecast - Repeating Values

[jwiller1]

Good Morning,

I am new here so I do not know if I will be using the correct terminology!

I have a vehicle replacement plan which I am forecasting out when items are to be replaced and then they may need to be replaced again in the 10 year plan depending on the optimum replacement time for each item.

I am having a problem with not being able to get the replacement value to repeat itself. I will give you an example (see below data):

There is an item which is due for replacement in FY 15/16 (this year) with a value of $23,840.33. I want to build into my existing formula to have this value repeat itself off the optimum replacement time(this varies; 2,3,4,5,6,7,8,9,10 years). ie the optimum replacement for this item is every 4 years, so I want it to also show the value in FY years: 19/20 & 23/24.

Here is my current formula to get the value in the first instance:

=IF(I6="DISPOSED",0,IF(IF(ISERROR(VLOOKUP(O6,'Year Table'!$C$3:$E$25,3,1)),0,VLOOKUP(O6,'Year Table'!$C$3:$E$25,3,1))=$U$1,P6,0))


Data example:

[TABLE="width: 1623"]
<tbody>[TR]
[TD]Optimum Replacement[/TD]
[TD]Est Replacement Date[/TD]
[TD]Est Purchase price
(Inc cmpnd int)[/TD]
[TD]Est trade Price[/TD]
[TD]2015/16[/TD]
[TD]2016/17[/TD]
[TD]2017/18[/TD]
[TD]2018/19[/TD]
[TD]2019/20[/TD]
[TD]2020/21[/TD]
[TD]2021/22[/TD]
[TD]2022/23[/TD]
[TD]2023/24[/TD]
[TD]2024/25[/TD]
[TD]2025/26[/TD]
[TD]2026/27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]3.00%[/TD]
[TD][/TD]
[TD]1/07/2015[/TD]
[TD]1/07/2016[/TD]
[TD]1/07/2017[/TD]
[TD]1/07/2018[/TD]
[TD]1/07/2019[/TD]
[TD]1/07/2020[/TD]
[TD]1/07/2021[/TD]
[TD]1/07/2022[/TD]
[TD]1/07/2023[/TD]
[TD]1/07/2024[/TD]
[TD]1/07/2025[/TD]
[TD]1/07/2026[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]20/09/2013[/TD]
[TD] $ 159,840.03[/TD]
[TD="align: right"]$27,480.38[/TD]
[TD] $ 159,840.03[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]18/11/2023[/TD]
[TD] $ 170,902.26[/TD]
[TD="align: right"]$44,442.41[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 170,902.26[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]25/05/2017[/TD]
[TD] $ 134,443.46[/TD]
[TD="align: right"]$34,961.45[/TD]
[TD] $ -[/TD]
[TD] $ 134,443.46[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]03/04/2016[/TD]
[TD] $ 23,840.33[/TD]
[TD="align: right"]$11,468.96[/TD]
[TD] $ 23,840.33[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]11/09/2021[/TD]
[TD] $ 109,582.88[/TD]
[TD="align: right"]$33,537.41[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 109,582.88[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10/06/2022[/TD]
[TD] $ 33,821.54[/TD]
[TD="align: right"]$10,350.95[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 33,821.54[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]01/09/2022[/TD]
[TD] $ 42,458.60[/TD]
[TD="align: right"]$12,994.29[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 42,458.60[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]13/02/2017[/TD]
[TD] $ 31,243.00[/TD]
[TD="align: right"]$15,030.20[/TD]
[TD] $ -[/TD]
[TD] $ 31,243.00[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]04/12/2016[/TD]
[TD] $ 33,503.11[/TD]
[TD="align: right"]$16,117.48[/TD]
[TD] $ -[/TD]
[TD] $ 33,503.11[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]01/04/2018[/TD]
[TD] $ 30,393.24[/TD]
[TD] $ 14,621.40[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 30,393.24[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]01/10/2013[/TD]
[TD] $ 22,884.31[/TD]
[TD="align: right"]$7,819.54[/TD]
[TD] $ 22,884.31[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]14/08/2019[/TD]
[TD] $ 37,785.99[/TD]
[TD="align: right"]$18,177.86[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 37,785.99[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]28/03/2017[/TD]
[TD] $ 43,572.53[/TD]
[TD] $ 20,961.62[/TD]
[TD] $ -[/TD]
[TD] $ 43,572.53[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]23/04/2019[/TD]
[TD] $ 35,794.25[/TD]
[TD="align: right"]$17,219.69[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 35,794.25[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]26/09/2016[/TD]
[TD] $ 30,734.92[/TD]
[TD="align: right"]$14,785.77[/TD]
[TD] $ -[/TD]
[TD] $ 30,734.92[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]01/05/2016[/TD]
[TD] $ 32,665.70[/TD]
[TD="align: right"]$15,714.62[/TD]
[TD] $ 32,665.70[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]27/08/2018[/TD]
[TD] $ 33,812.42[/TD]
[TD="align: right"]$16,266.28[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ 33,812.42[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
</tbody>[/TABLE]


If anyone could help it would be greatly appreciated,

Thanks in advance,

jwiller1

 

[DILIPandey]

Hi jwiller,

One side you are asking about forecast and other side you are using vlookup function which will pick up the number from a defined table.
I am not sure about your query. Are you trying to calculate something or just picking up the first instance (which vlookup can do) ?


Regards,
DILIPandey

 

[jwiller1]

Hi DILIPandey,

I am trying to pick something up, but the vlookup is doing a check to match other cells to return the cells I want, which is why I have need to use the nested IF.

I have been able to work out what I need, whether it is the best way to do it is another thing! The following is the formula what I have used.

=IF(T3="DISPOSED",0,IF(IF(ISERROR(VLOOKUP(AB3,'Year Table'!$C$3:$E$25,3,1)),0,VLOOKUP(AB3,'Year Table'!$C$3:$E$25,3,1))=$AI$1,AC3,IF($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1)=AA3,AC3,IF(SUM($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1))/2=AA3,AC3,IF(SUM($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1))/3=AA3,AC3,IF(SUM($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1))/4=AA3,AC3,IF(SUM($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1))/5=AA3,AC3,IF(SUM($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1))/6=AA3,AC3,IF(SUM($AI$2-VLOOKUP(AB3,'Year Table'!$B$3:$E$25,3,1))/7=AA3,AC3,0)))))))))

An example of the results I get (note cell references do not march, highlighted are the rep),

"Optimum
Replacement
YEARS" Est Replacement Date Est Purchase price 2015/16 2016/17 2017/18 2018/19 2019/20 2020/21 2021/22
6 19/04/2016 $67,382.54 $67,382.54 $- $- $- $- $- $67,382.54
4 22/12/2016 $40,883.43 $- $40,883.43 $- $- $- $40,883.43 $-
4 08/03/2017 $40,909.63 $- $40,909.63 $- $- $- $40,909.63 $-
2 11/08/2016 $30,033.11 $- $30,033.11 $- $30,033.11 $- $30,033.11 $-
2 02/12/2016 $29,504.59 $- $29,504.59 $- $29,504.59 $- $29,504.59 $-
4 19/04/2015 $27,885.33 $27,885.33 $- $- $- $27,885.33 $- $-


Thanks,
jwiller1

 

[DILIPandey]

Hi jwiller1,

Would be helpful if you post sample workbook on a public file sharing portal and share the link here.
Also indicate your expected results as well.

Regards,
DILIPandey