Home 
Search 
Today's Posts 
#1




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




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




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




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: =C2C2/(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 
Display Modes  


Similar Threads  
Thread  Forum  
Does anyone have a template for creating a slip chart in Excel  Charts and Charting in Excel  
Creating a template with excel  Excel Discussion (Misc queries)  
I Need Help Creating or Finding an Excel Template  Excel Worksheet Functions  
Creating a template in Excel?  Excel Discussion (Misc queries)  
Is there a template for creating outline in Excel as opposed to W  Excel Worksheet Functions 