Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 - |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
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 |