Excel Template (Creating a Function in the template)
 

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,
lets 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

Excel Template (Creating a Function in the template)
 

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.


"Té" wrote:

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,
lets 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

Excel Template (Creating a Function in the template)
 

or

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


On 6 Mar, 08:01, JBeaucaire
wrote:
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.



"Té" wrote:
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,
lets 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- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

Excel Template (Creating a Function in the template)
 

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!


"JBeaucaire" wrote:

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.


"Té" wrote:

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,
lets 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