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]

Your current formula doesn't ensure the distance and magnitude requirements are met at the same time. Instead it takes the first true (E39<50) and will return the result of AND(D37>=5)*E43*8...so if D37>=5 is TRUE, the AND functions returns a 1 and you'll get E43*8. If D37<5, the AND expression returns a 0 and you'll get 0. I don't think this type of formula is what you want.

 

[KRice]

I don't understand the magnitude brackets. Earlier you mentioned a magnitude of 50. How does that relate to the five brackets described in post #10 (four of which have the number "50" in them?

 

[Andyc516b]

There are 4 possible distance brackets
(0.5-5m, 5.1-10m, 10.1-30m, 30m plus)
And 5 magnitude brackets (50%+gas, 5-50% gas,50-99lel, 1-50lel, no trace) which makes for 20 different combinations.

On the current manual form, the distances are across the top and strength of reading down the side, you then cross reference the 2 to find the multiplier.

This is repeated for the closest and the highest reading to give 2 totals and the higher of the 2 is used to give the final risk score.

Unfortunately I can’t take a picture and upload it as even cropped to the maximum it’s too big and the work tablet will not open the attachment to put the work sheet up for editing

I can manually enter the multipliers from the table into the formulas, in effect I need a formula which says ‘if the gas reading (E39) is above 50 gas AND the distance is between 0.5-5m then multiply the site score (E43) by 8, if E39 is between 5-50 and the distance is over 30 then multiply the site score by 1’ the way I started doing it would require 20 IF/ANDs, to cover every combination of distance to gas reading. To calculate the total risk for the highest reading
I’d then have to repeat the process using different cell references for the closest reading.

 

[Andyc516b]

I don't understand the magnitude brackets. Earlier you mentioned a magnitude of 50. How does that relate to the five brackets described in post #10 (four of which have the number "50" in them?

The magnitudes are measured in percentage of gas 50+ or 5-50, below 5% gas is outside the explosive limits and so is expressed as a percentage of the lower explosive limit (1-99 LEL) with 0lel effectively being no trace
Therefore on the workbook I have started there is a cell for gas% readings E39 and another for LEL readings (E41)
The first 4 formulas would start if E39 is above 5 and d37 is 0.5-5 then E43 should be multiplied by (x) …….repeated for each distance bracket
The next 4 would start if E39 is 5-50 …….repeated for each distance bracket
The 4 after that would start if E41 is 50-99…….repeated for each distance bracket
If E41 is 1-49 …….
If E41 is 0…..

 

[KRice]

I almost understand...sorry, but a few more questions:

1. What happens if the distance is less than 0.5 m?
2. A higher concentration of gas (let's say 60 % of the subject gas mixed with air) is fundamentally different from 60 % LEL of the subject gas (which would be 60 % of whatever the LEL happens to be for that particular gas). What nomenclature do you use for these two cases?...something like "60" for the former and "60 LEL" for the latter?
3. Do you want to calculate all of the possibilities (for all distances, given a magnitude) or are you trying to determine the two relevant factors given both inputs of distance and magnitude?

 

[Andyc516b]

I almost understand...sorry, but a few more questions:

1. What happens if the distance is less than 0.5 m?
2. A higher concentration of gas (let's say 60 % of the subject gas mixed with air) is fundamentally different from 60 % LEL of the subject gas (which would be 60 % of whatever the LEL happens to be for that particular gas). What nomenclature do you use for these two cases?...something like "60" for the former and "60 LEL" for the latter?
3. Do you want to calculate all of the possibilities (for all distances, given a magnitude) or are you trying to determine the two relevant factors given both inputs of distance and magnitude?

If the distance is less than 0.5m then no risk score is raised as a team is immediately dispatched to the site, the risk score is used in situations where no immediate threat is present and the repair can be scheduled in for a later time. The higher the risk score the more urgent the repair.

Anything above 5% gas is simply recorded as ‘gas’ anything below 5% gas s recorded as a fraction of the explosive threshold so 20lel would be 1% gas but recorded as 20lel for the purposes of the risk score. Hence the 2 cells one for a true ‘gas’ reading (5%+) and one for LEL readings.

I would record my highest found magnitude and how far it was from property and the closest reading to property snd the magnitude of it. For example a reading of 60% gas 30m from property would get a lesser score than 20% gas 1m from the property.
We calculate the risk score for each (site score x the multiplier number found in the table I described) and which ever is the higher is used as the official risk score.
I hope this is clear.

 

[Snakehips]

Not sure I have as good an understanding as Kirk but I will take a look at this tomorrow and respond if it might be of value.
Just one question. will there be a lel value returned in E41 (and to be ignored) if gas% is 5% +
And alternatively, if less that 5% gas does E39 still show that low % value or not, given that the lel value in E41 will then be the value of interest?

 

[Andyc516b]

Not sure I have as good an understanding as Kirk but I will take a look at this tomorrow and respond if it might be of value.
Just one question. will there be a lel value returned in E41 (and to be ignored) if gas% is 5% +
And alternatively, if less that 5% gas does E39 still show that low % value or not, given that the lel value in E41 will then be the value of interest?

Thanks any help is hugely appreciated.
It’s an either/or situation, if only Lel readings are found then E39 would be left empty, if in the case of a large escape the highest AND closest readings could both be in the gas% and lel (E41) would be empty. The third possibility is that the highest is in the gas range but the closest is in the lel bracket. (Imagine a gas main has ruptured 20m from a property and a pocket of gas is creeping under sealed tarmac roadways and drives, at the site of the rupture would be a very high gas reading but a high distance would bring the score multiplier down, the edge of the pocket may only be an 80lel reading but it could be 0.6m from a property and therefore have a higher multiplier despite being a lower actual reading.
I will attempt to add a picture of the table we use to find the multiplier as it’ll probably be much clearer than I’m making it seem

 

[KRice]

Thanks, these answers have helped clarify things for me.

 

[KRice]

I would suggest thinking about your Magnitude-Distance risk factors table as having three coordinates: magnitude, distance, and scale, the latter of which indicates whether the LEL or gas part of the table should be considered. If you do this, and your risk factors table is structured similar to the one shown in this example, you can readily perform a two-way lookup on the rows and columns to find the risk factor corresponding to your three input coordinates. Starting with the upper blocks, I'm assuming you have a Site Risk Factor lookup table or some scheme for determining the site-specific factor based on knowledge of the location. Here I'm showing a simple lookup on the blue cell (D26) to return the matching Site Risk Factor in the table.

Next, you describe a Magnitude-Distance Risk Factor table similar to that shown highlighted in yellow. To facilitate lookups, I created the three lookup coordinates around this table, consisting of the lower limits on the distance tiers (orange), the scale (purple) indicating "gas" which applies to the upper two rows of the table and "lel" which applies to the lower three rows), and the magnitude upper limits (also purple). Then we can construct filtering criteria to determine the row and column that hold the risk factor of interest. As I understand them, the three criteria would be:

1. (F31 = $K$33:$K$37) matches the input "gas" or "lel" scale type to limit the rows considered
2. (E31 <= $J$33:$J$37) ensures the input magnitude is less than or equal to the upper limits of each magnitude tier, thus constraining the rows
3. (D31 > $M$31:$P$31) ensures the input distance is greater than the lower limits of each distance tier, thus constraining the columns

Check these carefully as I am not certain about the decision points. For example, your earlier post mentioned <50 while the Mag-Dist Risk Factor table suggests "50%+" and I don't know whether this means a magnitude of exactly 50 falls into the upper row or the second row of the risk factor table. Resolving the formula over this issue is trivial. These filtering criteria are used as arguments in the XMATCH function to determine which row and column should be selected. Here the direction of search matters as there may be several rows or columns that meet the criterial. The "-1" argument in the XMATCH functions takes the 1st match when the array is searched in the reverse direction, which I believe is necessary given the structure of the lookup table. If your actual lookup table has a different structure, this parameter may not be correct. Finally, the results of the XMATCH functions produce a single number indicating the row and column indices of the lookup table, and the INDEX function is used to extract the value at the intersection of those row/column coordinates.

Beyond that, the maximum Magnitude-Distance Factor is determined of the two input scenarios (largest mag and closest dist), then a list summarizes any scenario producing the maximum M-D factor, and then the product of the max M-D risk factor and the site-specific factor is taken.

I added a Data Validation selection feature for the "gas" or "lel" input for convenience, speed, and to enforce consistency with the lookup table terms. With this approach, IFS and nested IF functions can be avoided and the risk factors do not need to be hard-coded into the formula. The user input block (D31:F32) is more consolidated, and could easily be expanded to include other measurement scenarios to determine risk factors for them using the same formula.

If you are not familiar with the XL2BB add-in, a quick tip... you can click on the clipboard copy icon in the upper left at the intersection of the row/column labels to copy this working sample to your clipboard, then paste it directly into a blank worksheet to try it out. I would suggest pasting it only after navigating to and selecting cell C25 to match the same upper left corner cell shown in this example...I'm not certain if this matters, but it ensures the content will land at the same locations as the source.

MrExcel_20220401.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
25								Location	Site Factor
26	location	site C						site A	4
27	site-specific factor	2						site B	1.5
28								site C	2
29								site D	3
30		Distance (m)	Magnitude	Scale		M-D Factor
31	for largest reading	8	50	gas		3				Lower Limit (m) ->	0.5	5	10	30
32	for closest reading	6	20	lel		2.3		Upper Limit	Scale	Mag\Dist (m)	0.5-5	5.1-10	10.1-30	30+
33								100	gas	50%+gas	8	7	6	5
34								50	gas	5-50% gas	4	3	2	1
35	Max M-D Factor	Distance (m)	Magnitude	Scale		M-D Factor		99	lel	50-99lel	3	2.4	1.8	0.9
36	for largest reading	8	50	gas		3		50	lel	1-50lel	2.8	2.3	1.7	0.8
37								1	lel	no trace	0.5	0.4	0.4	0.2
38
39	Max Risk Factor	6
Sheet2

Cell Formulas
Range	Formula
D27	D27	=XLOOKUP(D26,$J$26:$J$29,$K$26:$K$29,"not found")
H31:H32	H31	=INDEX($M$33:$P$37,XMATCH(1,(F31=$K$33:$K$37)*(E31<=$J$33:$J$37),0,-1),XMATCH(1,--(D31>$M$31:$P$31),0,-1))
C36:F36	C36	=FILTER(C31:F32,H31:H32=H36)
H36	H36	=MAX(H31:H32)
D39	D39	=D27*H36
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
F31:F32	List	gas,lel