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]

Welcome to the Board! If possible, it would be very helpful if you could upload a small working example of your sheet (edited to eliminate any sensitive information) using the XL2BB add-in available here (see link in my signature block). If that is problematic, then link to a small sample worksheet (Dropbox, Google Drive, etc.) or, as a last resort, upload a partial screenshot showing the general layout of your worksheet and the ranges where the lookup information can be found.

 

[KRice]

I can see that this part of your expression is a problem: AND(D37<5>10)
Do you want to check that D37 is less than 5 AND D37 is also greater than 10? AND(D37<5, D37>10)

 

[Andyc516b]

Many thanks I am currently on my mobile, I will log in when I get home and send from my tablet I’m working on, assuming work will allow me to install the program on their device
I tried uploading a screen shot but the image was too big.

 

[Andyc516b]

I can see that this part of your expression is a problem: AND(D37<5>10)
Do you want to check that D37 is less than 5 AND D37 is also greater than 10? AND(D37<5, D37>10)

No I want to check if d37 is more than 5 but Less than 10, if it is 9 for example if it is then the score would be multiplied by 7 if it was 13 say move onto next check

 

[KRice]

Sorry...just looked at what I wrote before and realized it makes no sense...it would never be true!
It should read: AND(D37>5, D37<10)

Work restrictions might very well limit what you can install. A snapshot using the snipping tool will allow you to include a smaller selection of cells.

 

[Snakehips]

@Andyc516b
Hopefully you will be able to post a visual that illustrates what you have and what you need.
If not then please do try and clarify.

I am a little confused by your text which refers to 'if a reading above 50 is less than 5m away the site total would be multiplied by 8'.
Yet your formula snippet has 'IFS(E39<50...... ?
You also refer to '20 possible combinations of distance against strength'. Are you able to define them?

IFS(E39<50,AND(D37>=5)*E43*8,
(E39<50,AND(D37<5>10)*E43*7,
Etc etc suggests subsequent multipliers 6,5,4,3,2,1 ???? That would be 8 combinations.
Are there then others using a distance other than 50?

 

[KRice]

Depending on the structure of the magnitude-distance (M-D) factor table, there may be a more efficient way to determine the factors. Here is a mock-up, partially based on the information provided. Is the primary issue one of determining automatically which M-D factor applies for the closest distance condition and which M-D factor applies for the largest magnitude condition? If so, then more details are needed to assess whether there might be better approaches based on referencing the risk factor table rather than hard-wiring the thresholds directly into the formulas. In this example, the 20 row M-D factor lookup table has a structure such that we could filter the factors based on meeting certain criteria (such as relevant rows must have a magnitude greater than the measurement AND the corresponding distance in the lookup table must be less than the actual distance). This potentially returns no matches, one, or even multiple matches. Depending of what the specific filtering rules are, this approach might be of interest. Assumed inputs are shaded light blue. In this example, you'll see where the formula returns two matches for both cases.

MrExcel_20220401.xlsx
	C	D	E	F	G	H	I	J	K	L	M
33					Idx	Magnitude	Dist. (m)	M-D Factor		Location	Site Factor
34					1	60	5	9.3		site A	4
35					2	60	10	8.3		site B	1.5
36					3	60	15	7.3		site C	2
37	distance to largest reading	8			4	60	20	6.3		site D	3
38					5	60	25	5.3
39	magnitude of largest reading	--->	49		6	50	5	8
40	M-D factor for largest reading	--->	9.3		7	50	10	7
41			8		8	50	15	6
42	location?	--->	site C		9	50	20	5
43	site-specific factor	--->	2		10	50	25	4
44					11	40	5	6.5
45	distance to closest reading?	6			12	40	10	5.5
46					13	40	15	4.5
47	magnitude of closest reading?	--->	48		14	40	20	3.5
48	M-D factor for largest reading	--->	9.3		15	40	25	2.5
49			8		16	30	5	5.1
50					17	30	10	4.1
51					18	30	15	3.1
52					19	30	20	2.1
53					20	30	25	1.1
Sheet2

Cell Formulas
Range	Formula
E40:E41,E48:E49	E40	=FILTER($J$34:$J$53,($H$34:$H$53>E39)*($I$34:$I$53<D37),"not found")
E43	E43	=XLOOKUP(E42,$L$34:$L$37,$M$34:$M$37,"not found")
Dynamic array formulas.

 

[Andyc516b]

Sorry...just looked at what I wrote before and realized it makes no sense...it would never be true!
It should read: AND(D37>5, D37<10)

Work restrictions might very well limit what you can install. A snapshot using the snipping tool will allow you to include a smaller selection of cells.

I have tried editing as you suggested, the First logical works but if the numbers don't fit those parameters it just gives a zero

 

[Andyc516b]

@Andyc516b
Hopefully you will be able to post a visual that illustrates what you have and what you need.
If not then please do try and clarify.

I am a little confused by your text which refers to 'if a reading above 50 is less than 5m away the site total would be multiplied by 8'.
Yet your formula snippet has 'IFS(E39<50...... ?
You also refer to '20 possible combinations of distance against strength'. Are you able to define them?

IFS(E39<50,AND(D37>=5)*E43*8,
(E39<50,AND(D37<5>10)*E43*7,
Etc etc suggests subsequent multipliers 6,5,4,3,2,1 ???? That would be 8 combinations.
Are there then others using a distance other than 50?

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.