Excel Template (Creating a Function in the template)

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
 
J

JBeaucaire

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?
 
J

Jarek Kujawa

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 -
 
R

Ron@Buy

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top