Excel Template (Creating a Function in the template)

  • Thread starter Thread starter Té
  • Start date Start date
T

Té

Help! I 've found a wonderful template (Inventory List) And in the template I
am trying to create a funtion.

Our signs depreciate over a 24 month period down to a zero book value. So,
let’s say I paid $15,895.00 for this sign on 01/01/09; this means that the
sign loses $662.29(cost of sign divided by 24 months) worth of value each
month. So, on 02/01/09 the sign would be worth $15,232.71 and as of 03/01/09
it would be worth $14570.42 and so on…losing $662.29 worth of value every
month for 24 months.

I found that the template works for every other aspect of data I need to
capture once I add filters. J But, I want to know if there is a way to make
the Current Value column update automatically based on the month (or daily if
we have to) you open the document in. I want to always see the absolute
current value of the sign at all times
 
As long as we know where the data is, this is doable.

For this example (adjust for your data), I'm putting the date the sign was
created in B2, and the original value in C2.

In D2, to show the current value at all times, use this formula:

=C2-(C2/24)*DATEDIF(B2,TODAY(),"m")

Does that work for you?
 
or

=C2-(MONTH(B2)-MONTH(TODAY()))*C2/24


As long as we know where the data is, this is doable.

For this example (adjust for your data), I'm putting the date the sign was
created in B2, and the original value in C2.

In D2, to show the current value at all times, use this formula:

=C2-(C2/24)*DATEDIF(B2,TODAY(),"m")

Does that work for you?

--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.







- Pokaż cytowany tekst -
Click to expand...
 
I think you may have missed the OPs "(or daily if we have to)" so maybe a
closer result using your cell references (A2 contains the number of months
depreciation) will be obtained with: =C2-C2/(A2*365)*(TODAY()-B2). Note this
ignores leap years!
 
Back
Top