Tracking of a declining/increasing value from month to month

[tesleen2025]

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?

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

 

[jorismoerings]

Hi,

Take a look at this and see if this is what your after;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">9</td><td style="font-weight: bold;;">Event Date</td><td style="font-weight: bold;;">Time Weight</td><td style="font-weight: bold;;">Severity</td><td style="font-weight: bold;;">Points</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">12/11/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">2</td><td style="text-align: right;;"> 6 </td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">12/11/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">4</td><td style="text-align: right;;"> 12 </td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">12/11/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">4</td><td style="text-align: right;;"> 12 </td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">12/8/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">8</td><td style="text-align: right;;"> 24 </td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">12/7/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">2</td><td style="text-align: right;;"> 6 </td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">12/7/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">4</td><td style="text-align: right;;"> 12 </td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">12/7/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">4</td><td style="text-align: right;;"> 12 </td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">12/6/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">2</td><td style="text-align: right;;"> 6 </td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">12/2/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">2</td><td style="text-align: right;;"> 6 </td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">11/30/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">1</td><td style="text-align: right;;"> 3 </td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">11/30/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">4</td><td style="text-align: right;;"> 12 </td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">11/30/2016</td><td style="text-align: right;;"> 3 </td><td style="text-align: right;;">1</td><td style="text-align: right;;"> 3 </td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B10</th><td style="text-align:left">{=IFERROR(<font color="Blue">LOOKUP(<font color="Red">A10,EDATE(<font color="Green">TODAY(<font color="Purple"></font>),{-24;-12;-6}</font>),{1;2;3}</font>),0</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[tesleen2025]

jorismoerings,
Thank you for your attempt to help but this formula gives me the time weight which is part of the data I already have.

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.

Thank you,
Tracie

 

[jorismoerings]

Hi,

Misunderstood;
so the following questions:

1. do you download the monthly information on a separate worksheet or do you add them to an existing one.
2. if you add them to an existing one, is there some way to distinguish between the 2 downloads.

 

[tesleen2025]

Download monthly, new data overwrites old data, has it's own tab in the workbook.

I am open to suggestions.