tesleen2025
New Member
- Joined
- Nov 23, 2016
- Messages
- 13
Hi,
I am seeking a way to keep track of a declining/increasing value from month to month.
Each month I download information that does all of the following…
1) Updates existing information with new value
2) Adds new information and values
3) Drops old information over 24 months old
Values are calculated as (time weight * severity).
0-6mo time weight = 3
6-12mo time weight = 2
12-24mo time weight = 1
>24mo time weight = 0
Example: time weight 3 * severity 2 = 6 points
time weight 2 * severity 2 = 4 points
Each month I need to track new points as they are added
I also need to track how many points drop off each month keeping these separate from the new points, in other words if last month we had an event that was added for 12 points and an event that was dropped by 2 points I need to track them separate. I cannot show an overall change of 10.
Does anyone have an idea of how I can do this with a formula or macro?
<tbody>
</tbody>
Thank you,
Tracie
I am seeking a way to keep track of a declining/increasing value from month to month.
Each month I download information that does all of the following…
1) Updates existing information with new value
2) Adds new information and values
3) Drops old information over 24 months old
Values are calculated as (time weight * severity).
0-6mo time weight = 3
6-12mo time weight = 2
12-24mo time weight = 1
>24mo time weight = 0
Example: time weight 3 * severity 2 = 6 points
time weight 2 * severity 2 = 4 points
Each month I need to track new points as they are added
I also need to track how many points drop off each month keeping these separate from the new points, in other words if last month we had an event that was added for 12 points and an event that was dropped by 2 points I need to track them separate. I cannot show an overall change of 10.
Does anyone have an idea of how I can do this with a formula or macro?
Event Date | Time Weight | Severity | Points |
12/11/2016 | 3 | 2 | 6 |
12/11/2016 | 3 | 4 | 12 |
12/11/2016 | 3 | 4 | 12 |
12/8/2016 | 3 | 8 | 24 |
12/7/2016 | 3 | 2 | 6 |
12/7/2016 | 3 | 4 | 12 |
12/7/2016 | 3 | 4 | 12 |
12/6/2016 | 3 | 2 | 6 |
12/2/2016 | 3 | 2 | 6 |
11/30/2016 | 3 | 1 | 3 |
11/30/2016 | 3 | 4 | 12 |
11/30/2016 | 3 | 1 | 3 |
<tbody>
</tbody>
Thank you,
Tracie