Log In
Register
Home
October 11, 2012
Offline
Hi Guys
Need your help.
I want to put a formula in the attached sample worksheet as per below requirement.
In the first month of purchase, the value will remain the same, after this, it will depreciate by 30% i.e. the book value will be 70%.
Please help me. The file is attached.
Regards
Ali
See AnswerJune 25, 2020
Offline
Hi Ali,
I assume you want the depreciation data to continue to the end of the year
If so, then only one formula needed:
clear cells F3:Q5 and type this into cell F3
=IF(F2:Q2<C3:C5,,B3:B5*(1-D3:D5)^DATEDIF(C3:C5,F2:Q2,"m"))
that should then spill across and down to complete your data
Furthermore, if you define the date series as a spilling formula (eg =DATE(2021,SEQUENCE(,12),1), then you can use this in the above formula too:
=IF(F2#<C3:C5,,B3:B5*(1-D3:D5)^DATEDIF(C3:C5,F2#,"m"))
or slightly less elegantly:
=IFERROR(B3:B5*(1-D3:D5)^DATEDIF(C3:C5,F2#,"m"),)
then, if you extend your date series (by changing the 12 to the desired no of months), your data formula will also extend itself to match
sometimes Excel is just magic!
jim
Answers Post
1 Guest(s)
