Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 6th 09, 07:31 AM posted to microsoft.public.excel.worksheet.functions
Té is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 76
Default 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,
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


  #2   Report Post  
Old March 6th 09, 08:01 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2009
Posts: 222
Default 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,
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

  #3   Report Post  
Old March 6th 09, 08:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2008
Posts: 896
Default 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,
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- Ukryj cytowany tekst -


- Pokaż cytowany tekst -


  #4   Report Post  
Old March 6th 09, 08:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 345
Default 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,
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Does anyone have a template for creating a slip chart in Excel Slip charts in Excel Charts and Charting in Excel 2 March 12th 08 01:54 PM
Creating a template with excel Tired of Setbacks Excel Discussion (Misc queries) 3 October 12th 07 03:35 PM
I Need Help Creating or Finding an Excel Template mscraven Excel Worksheet Functions 1 August 14th 06 03:27 AM
Creating a template in Excel? KGreen Excel Discussion (Misc queries) 1 September 21st 05 05:46 PM
Is there a template for creating outline in Excel as opposed to W Charlie Starnes Excel Worksheet Functions 0 June 22nd 05 07:49 PM


All times are GMT +1. The time now is 09:11 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017