OTOTO
Board Regular
- Joined
- Dec 23, 2013
- Messages
- 209
HI guys,
I need help writing a formula for trending risk status.
In a worksheet titled "Updates":
I have Risk IDs (unique #s) in column A.
I have non-adjusted Risk level ("L","LM","M","MH","H","F") and Adjusted risks level (same scale) in columns G and M. (This scale should likely be numerically indexed)
The old non-adjusted Risk level and adjusted Risk level in columns E and K (respectively).
Column C has the date of the updatesheet "Updates" that indicates when the change took place.
Let's assume that the start date is 1/1/2014 and that the updates are quarterly (January 1, April 1, July 1, October 1).
I'd like to have 3 functions. One to see the strength of trending over time in the long term or all update periods and another show just the change(s) in the last 1 or 2 quarterly updates. Another will show the consistency of the rating by giving a measure which shows change or static ratings of the risks.
The function will be added to a worksheet titled "RiskRegister". That worksheet also contains the Risk ID # in column A.
For most recent updates:
I want the function to find value in "RiskRegister" column A and search for corresponding value in "Update" column A. For any listings in the "Updates" sheet which occurred in the last 6 months (most recent 2 updates). Identifying whether the earliest updates column E and column K values are < or > than the most recent updates column G and M values and determine by how much (L-F can be indexed as 1-6 for this purpose) was the change.
For overall trending:
Same as above but the search would be for all listings in the "Update" worksheet that have column A matches.
For Consistency rating:
The search should output a coefficient that weighs the number of non changing update periods as a fraction of all update periods that have passed.
Thanks for your help in advance.
I need help writing a formula for trending risk status.
In a worksheet titled "Updates":
I have Risk IDs (unique #s) in column A.
I have non-adjusted Risk level ("L","LM","M","MH","H","F") and Adjusted risks level (same scale) in columns G and M. (This scale should likely be numerically indexed)
The old non-adjusted Risk level and adjusted Risk level in columns E and K (respectively).
Column C has the date of the updatesheet "Updates" that indicates when the change took place.
Let's assume that the start date is 1/1/2014 and that the updates are quarterly (January 1, April 1, July 1, October 1).
I'd like to have 3 functions. One to see the strength of trending over time in the long term or all update periods and another show just the change(s) in the last 1 or 2 quarterly updates. Another will show the consistency of the rating by giving a measure which shows change or static ratings of the risks.
The function will be added to a worksheet titled "RiskRegister". That worksheet also contains the Risk ID # in column A.
For most recent updates:
I want the function to find value in "RiskRegister" column A and search for corresponding value in "Update" column A. For any listings in the "Updates" sheet which occurred in the last 6 months (most recent 2 updates). Identifying whether the earliest updates column E and column K values are < or > than the most recent updates column G and M values and determine by how much (L-F can be indexed as 1-6 for this purpose) was the change.
For overall trending:
Same as above but the search would be for all listings in the "Update" worksheet that have column A matches.
For Consistency rating:
The search should output a coefficient that weighs the number of non changing update periods as a fraction of all update periods that have passed.
Thanks for your help in advance.
