Risk score form

[Andyc516b]

I am trying to put together a manual back up for an electronic system at work which on occasion fails.
The form allocates a risk score based on 2 factors the magnitude of a reading and the proximity of the reading.
Risk factors are calculated for the location and then a multiplier is added for each (the closest proximity and the highest recorded reading) giving 2 score which may or may not differ.
I have cells allocated to record the distance to the highest and distance to the closest reading and cells to record the magnitude of the highest overall reading and the closest reading. There is then a multiplier table so for example if a reading above 50 is less than 5m away the site total would be multiplied by 8 but if the same reading was between 5 and 10m away it would be multiplied by 7 and so on.

I have tried using ifs/and but it doesn’t seem to work For the above example I used
IFS(E39<50,AND(D37>=5)*E43*8,
(E39<50,AND(D37<5>10)*E43*7,
Etc etc
Where E39 is the strength of reading, D37 is the distance to the highest reading and E43 is the site specific score which is constant throughout the job.

There are 20 possible combinations of distance against strength.
Using the above formula if E39 is above 50 and D37 is below 5 the result is multiplied by 8 correctly, if either number falls out of that parameter I just get a zero as the result.
Is there a more elegant solution if not what am I doing wrong after the first logic test to only get zeros?

 

[KRice]

Thanks for the clarification. I'll offer another option, as it occurred to me that referencing the external risk factor table with the formula might not be practical. This version is almost the same as what I posted earlier, except the entire table of risk factors is included in the formula as an array (numbers on the same row are separated by commas, and rows are separated by semicolons), and no helper column/row labels are needed, as they too are incorporated in the formula in arrays. It sounds as if you prefer to have two separate inputs: one for gas% readings, the other for LEL% readings. Separate formulas (nearly identical) are used to determine the risk factor for each of the two magnitude inputs. There are (I believe) minor differences in how the gas% and LEL% scales are interpreted:

1. Given a magnitude in the gas% reading (in cell E40), this part of the expression allows values from the top two rows of the risk factor table to be considered: {1;1;0;0;0} ...and then this logic determines which rows to consider, using the lower threshold of each tier in array form: E40>{50;5;20;1;0}
2. Given a magnitude in the LEL% reading (in cell G40), this part of the expression allows values from the bottom three rows of the risk factor table to be considered: {0;0;1;1;1} ...and then this logic determines which rows to consider, using the lower threshold of each tier in array form: G40>={50;5;20;1;0}

Please let me know if the incorrect results are returned at the intersections of adjacent tiers where the > vs. >= logic places these decision points into one tier or the other.

All other parts of the formulas are the same:

1. This is the risk factor table in array form: {8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0}
2. Given a distance (in cell D37), this logic determines which columns of the risk factor table to be considered, using the lower threshold of each tier in array form: $D$37>{0.5,5,10,30}

The row and column logic described above produce arrays that the XMATCH functions search in particular directions to return the correct row and column; and then those row/column coordinates return the risk factor from the risk factor array.

MrExcel_20220401.xlsx
	D	E	F	G
36	Distance (m)
37	5
38
39	Scale	gas		lel
40	Magnitude (%gas or %lel)	50		18
41	M-D Factor	6		2
42	Maximum M-D Factor		6
43	Site Factor		2
44	Max Risk Factor		12
Sheet2 (2)

Cell Formulas
Range	Formula
E41	E41	=INDEX({8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0},XMATCH(1,{1;1;0;0;0}*(E40>{50;5;20;1;0}),0,1),XMATCH(1,--($D$37>{0.5,5,10,30}),0,-1))
G41	G41	=INDEX({8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0},XMATCH(1,{0;0;1;1;1}*(G40>={50;5;20;1;0}),0,1),XMATCH(1,--($D$37>{0.5,5,10,30}),0,-1))
F42	F42	=MAX(E41,G41)
F44	F44	=F43*F42

For convenience, the risk factor table is shown here:

MrExcel_20220401.xlsx
	I	J	K	L	M	N
36		Lower Limit (m) ->	0.5	5	10	30
37			Distance from Property
38	Lower Limit	Gas Readings	500mm- 5m	5.1m - 10m	10.1m-30m	greater than 30m
39	50	More than 50% GIA	8	7	4	1
40	5	5% GIA to 50% GIA	6	4	3	1
41	20	20 to 99%LEL	4	3	1	1
42	1	1 to 19%LEL	2	1	1	1
43	0	No Trace	0	0	0	0
Sheet2 (2)

 

[Andyc516b]

Thanks for the clarification. I'll offer another option, as it occurred to me that referencing the external risk factor table with the formula might not be practical. This version is almost the same as what I posted earlier, except the entire table of risk factors is included in the formula as an array (numbers on the same row are separated by commas, and rows are separated by semicolons), and no helper column/row labels are needed, as they too are incorporated in the formula in arrays. It sounds as if you prefer to have two separate inputs: one for gas% readings, the other for LEL% readings. Separate formulas (nearly identical) are used to determine the risk factor for each of the two magnitude inputs. There are (I believe) minor differences in how the gas% and LEL% scales are interpreted:

1. Given a magnitude in the gas% reading (in cell E40), this part of the expression allows values from the top two rows of the risk factor table to be considered: {1;1;0;0;0} ...and then this logic determines which rows to consider, using the lower threshold of each tier in array form: E40>{50;5;20;1;0}
2. Given a magnitude in the LEL% reading (in cell G40), this part of the expression allows values from the bottom three rows of the risk factor table to be considered: {0;0;1;1;1} ...and then this logic determines which rows to consider, using the lower threshold of each tier in array form: G40>={50;5;20;1;0}

Please let me know if the incorrect results are returned at the intersections of adjacent tiers where the > vs. >= logic places these decision points into one tier or the other.

All other parts of the formulas are the same:

1. This is the risk factor table in array form: {8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0}
2. Given a distance (in cell D37), this logic determines which columns of the risk factor table to be considered, using the lower threshold of each tier in array form: $D$37>{0.5,5,10,30}

The row and column logic described above produce arrays that the XMATCH functions search in particular directions to return the correct row and column; and then those row/column coordinates return the risk factor from the risk factor array.

MrExcel_20220401.xlsx
	D	E	F	G
36	Distance (m)
37	5
38
39	Scale	gas		lel
40	Magnitude (%gas or %lel)	50		18
41	M-D Factor	6		2
42	Maximum M-D Factor		6
43	Site Factor		2
44	Max Risk Factor		12
Sheet2 (2)

Cell Formulas
Range	Formula
E41	E41	=INDEX({8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0},XMATCH(1,{1;1;0;0;0}*(E40>{50;5;20;1;0}),0,1),XMATCH(1,--($D$37>{0.5,5,10,30}),0,-1))
G41	G41	=INDEX({8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0},XMATCH(1,{0;0;1;1;1}*(G40>={50;5;20;1;0}),0,1),XMATCH(1,--($D$37>{0.5,5,10,30}),0,-1))
F42	F42	=MAX(E41,G41)
F44	F44	=F43*F42

For convenience, the risk factor table is shown here:

MrExcel_20220401.xlsx
	I	J	K	L	M	N
36		Lower Limit (m) ->	0.5	5	10	30
37			Distance from Property
38	Lower Limit	Gas Readings	500mm- 5m	5.1m - 10m	10.1m-30m	greater than 30m
39	50	More than 50% GIA	8	7	4	1
40	5	5% GIA to 50% GIA	6	4	3	1
41	20	20 to 99%LEL	4	3	1	1
42	1	1 to 19%LEL	2	1	1	1
43	0	No Trace	0	0	0	0
Sheet2 (2)

I really appreciate the help, it may take me some time to digest all that and learn what most of what you said means. As I mentioned I used excel for the first time on Tuesday of this week to put a Timesheet hours calculator together my boss saw this and decided I am now the excel guy in the office and gave me this task.
Since the upload tool won’t work for me I assume emailing copy directly isn’t allowed?

 

[KRice]

I'm happy to help...and congratulations on your new designation as the Excel Guy in your office! Even if you can't install the XL2BB add-in, you should be able to copy each of the two formulas in my last post and paste them anywhere into your spreadsheet...and then change the two cell references in each (D37 for distance and either E40 for the gas% formula or G40 for the LEL% formula)) to point to the correct cells in your worksheet. I would recommend checking around all of the endpoints of each tier and each intersection between tiers to confirm that the formulas return expected values. As I mentioned earlier, it appears to me that there are some inconsistencies in how the row and column headings in the risk factor table are labeled, where the endpoints of each tier are sometimes to be included in that tier, and at other times they are not. I don't know if the apparent inconsistencies are real, or if the row/column labels are just poorly described. Post back if you notice any discrepancies between expected answers and those generated by the formulas. These formulas do not have error checking or exceptions built into them (they could if desired),...to handle situations where, for example, gas%<=5, LEL%>99, etc.

 

[Andyc516b]

I'm happy to help...and congratulations on your new designation as the Excel Guy in your office! Even if you can't install the XL2BB add-in, you should be able to copy each of the two formulas in my last post and paste them anywhere into your spreadsheet...and then change the two cell references in each (D37 for distance and either E40 for the gas% formula or G40 for the LEL% formula)) to point to the correct cells in your worksheet. I would recommend checking around all of the endpoints of each tier and each intersection between tiers to confirm that the formulas return expected values. As I mentioned earlier, it appears to me that there are some inconsistencies in how the row and column headings in the risk factor table are labeled, where the endpoints of each tier are sometimes to be included in that tier, and at other times they are not. I don't know if the apparent inconsistencies are real, or if the row/column labels are just poorly described. Post back if you notice any discrepancies between expected answers and those generated by the formulas. These formulas do not have error checking or exceptions built into them (they could if desired),...to handle situations where, for example, gas%<=5, LEL%>99, etc.

I will try and work it out and to translate what’s you’ve given me to my workbook.
I’m sure it’s poorly worded on the sheet. Or I have described poorly how the machines work.

 

[KRice]

No worries! I'm mostly concerned about the following two conditions:

1. distance = 0.5 m and whether that is supposed to go into the "500mm-5m" tier. As written, distance<=0.5 will produce an error. Other distance tiers use the upper end of the tier below them as the dividing point, but magnitude readings exactly at the dividing points belong to the lower tier. If 0.5 m belongs to "500mm-5m" rather than the urgent category, then it represents an exception to the convention used on the distance tiers (columns), and the easiest fix would be to change "0.5" to a slightly different value in the arrays showing the lower thresholds of the distance tiers.
2. gas% = 5 % and whether that belongs to the "5% to 50% "tier. As written, gas%<=5 will produce an error. If is does belong to that tier, then it also represents a change from how the other gas% tier is handled (where the lower bound of the range is not included in the tier). If a problem is noted here, the easiest fix would be to change "5" to a slightly different value in the arrays showing the lower thresholds of the magnitude tiers.

In various other posts, you've mentioned how certain measurement results would be handled that are not reflected in the risk factor table or the formulas.

1. Should there be an error message: for gas% > 100?... for LEL% > 99?... for any magnitude < 0?... for distance < 0?
2. Should "urgent" be displayed if distance is less than (or less than or equal to) 0.5 m?
3. Should "LEL required" be displayed if gas% is less than (or less than or equal to) 5 %?

 

[KRice]

If concerns 1 and 2 in my last post describe real issues, then this version uses the "easiest fix" approach mentioned (changing the thresholds very slightly to account for the exceptions)...and we assume the amount of the change (say from 0.5 m to 0.499 m is less than the precision of any measurement being recorded. This version also includes some error trapping and messaging described in the questions I asked: the approximate first half of each formula performs this checking/messaging.

MrExcel_20220401.xlsx
	D	E	F	G
5	Distance (m)
6	5
7
8	Scale	gas		lel
9	Magnitude (%gas or %lel)	50		20
10	M-D Factor	6		4
11	Maximum M-D Factor		6
12	Site Factor		2
13	Max Risk Factor		12
Sheet2 (2)

Cell Formulas
Range	Formula
E10	E10	=IFS(AND(0<=D6,D6<0.5),"urgent, small dist",OR(E9>100,E9<0,D6<0),"input out of range",E9<5,"LEL% req'd",1,INDEX({8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0},XMATCH(1,{1;1;0;0;0}*(E9>{50;4.999;20;1;0}),0,1),XMATCH(1,--(D6>{0.499,5,10,30}),0,-1)))
G10	G10	=IFS(AND(0<=D6,D6<0.5),"urgent, small dist",OR(G9>99,G9<0,D6<0),"input out of range",1,INDEX({8,7,4,1;6,4,3,1;4,3,1,1;2,1,1,1;0,0,0,0},XMATCH(1,{0;0;1;1;1}*(G9>={50;4.999;20;1;0}),0,1),XMATCH(1,--(D6>{0.499,5,10,30}),0,-1)))
F11	F11	=MAX(E10,G10)
F13	F13	=F12*F11

 

[Andyc516b]

No worries! I'm mostly concerned about the following two conditions:

1. distance = 0.5 m and whether that is supposed to go into the "500mm-5m" tier. As written, distance<=0.5 will produce an error. Other distance tiers use the upper end of the tier below them as the dividing point, but magnitude readings exactly at the dividing points belong to the lower tier. If 0.5 m belongs to "500mm-5m" rather than the urgent category, then it represents an exception to the convention used on the distance tiers (columns), and the easiest fix would be to change "0.5" to a slightly different value in the arrays showing the lower thresholds of the distance tiers.
2. gas% = 5 % and whether that belongs to the "5% to 50% "tier. As written, gas%<=5 will produce an error. If is does belong to that tier, then it also represents a change from how the other gas% tier is handled (where the lower bound of the range is not included in the tier). If a problem is noted here, the easiest fix would be to change "5" to a slightly different value in the arrays showing the lower thresholds of the magnitude tiers.

In various other posts, you've mentioned how certain measurement results would be handled that are not reflected in the risk factor table or the formulas.

1. Should there be an error message: for gas% > 100?... for LEL% > 99?... for any magnitude < 0?... for distance < 0?
2. Should "urgent" be displayed if distance is less than (or less than or equal to) 0.5 m?
3. Should "LEL required" be displayed if gas% is less than (or less than or equal to) 5 %?

sorry for delayed reply, I have been trying to decipher and understand your previous posts and formulas but it is mostly over my head.
to answer your questions, a reading of above 100% gas, below 0 lel or for negative distances would not show up on our measuring equipment and therefore would only be inputted through user error, whilst I would like to think most staff are experienced enough to realise this an error message/prompt would be beneficial but not strictly necessary

 

[Andyc516b]

I’d like to thank both of you for you help and efforts, given time and practice/research I may understand what you’ve said well enough to implement it. I think for now I’m going to have to admit defeat and tell my boss I’m not up to the task.
Sorry for wasting your time. If you can suggest any reading material or online resources to build me up from the fundamentals I’d appreciate it, I think made the mistake of jumping in half ****ed and got lucky on making a working timesheet/pay tracker through trial and error and luck.

 

[KRice]

Understood...I agree with your point about the user input error. Give the latest version in post #26 a try, just change the input references (in my formula they are D6 (distance), E10 (gas% reading), and G10 (lel% reading) to match the cell references in your worksheet to those same quantities. The rest of the formula contains your risk factor table and does the more complicated column/row cross referencing. This latest version of the formula has the input error checking in place. In my view, the best place to start learning is to choose a problem that is meaningful to you (like the problem you presented). Study the structure of the formulas suggested, and most importantly, break the formulas apart starting from the inside and read about what the various functions in the formula do and what the correct syntax for them is. One of the most convenient references is help offered by Microsoft directly from Excel, where you can search for help on the functions.

 

[Andyc516b]

Understood...I agree with your point about the user input error. Give the latest version in post #26 a try, just change the input references (in my formula they are D6 (distance), E10 (gas% reading), and G10 (lel% reading) to match the cell references in your worksheet to those same quantities. The rest of the formula contains your risk factor table and does the more complicated column/row cross referencing. This latest version of the formula has the input error checking in place. In my view, the best place to start learning is to choose a problem that is meaningful to you (like the problem you presented). Study the structure of the formulas suggested, and most importantly, break the formulas apart starting from the inside and read about what the various functions in the formula do and what the correct syntax for them is. One of the most convenient references is help offered by Microsoft directly from Excel, where you can search for help on the functions.

Thanks again, I will try copying your formulas directly into my work book then.
I like to understand the process behind things to fully understand them, the why things work is more important to me than them simply working. If that makes sense.
I will do more reading but will attempt to patch your workings into my sheet to keep my boss happy with the condition that he’s fully aware stuff like this is massively above my ability and it’s not my work