Tracking of a declining/increasing value from month to month

tesleen2025

New Member
Hi,
I am seeking a way to keep track of a declining/increasing value from month to month.

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

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

jorismoerings

Well-known Member
Hi,

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

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

tesleen2025

New Member
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

Well-known Member
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

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

I am open to suggestions.

Replies
6
Views
392
Replies
4
Views
249
Replies
4
Views
193
Replies
0
Views
236
Replies
0
Views
167

1,191,026
Messages
5,984,209
Members
439,878
Latest member
melodysc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back