Jump to content

  • Quick Navigation
Photo

Risk matrix

Share this

  • You cannot start a new topic
  • Please log in to reply
1 reply to this topic
- - - - -

leila19

    Grade - AIFSQN

  • IFSQN Associate
  • 48 posts
  • 2 thanks
0
Neutral

  • Tunisia
    Tunisia

Posted 29 July 2021 - 01:00 PM

Hello, please can someone helps me to understand this formula: =SI(D4=Lists!$I$2;"1.0";SI($D4="";"";MOYENNE(RECHERCHEV($D4;Lists!$I$1:$Q$6;9;0);(RECHERCHEV($E4;Lists!$J$1:$Q$6;8;0)))))

 

Please see attached



johnmcip

    Grade - MIFSQN

  • IFSQN Member
  • 93 posts
  • 16 thanks
20
Excellent

  • United States
    United States

Posted 29 July 2021 - 03:02 PM

I don't see an attachment. It looks like its in French.

 

=IF(D4=Lists!$I$2;"1.0";IF($D4="";"";AVERAGE(VLOOKUP($D4;Lists!$I$1:$Q$6;9;0);(VLOOKUP($E4;Lists!$J$1:$Q$6;8;0)))))

 

Above is translated. Lemme try to interpret it. I assume french syntax is the same as english.

 

---

 

If D4 is I2, then its 1 (not sure why this is a text string)

if D4 is NOT I2, then proceed

if D4 is blank, this cell is blank (typically used to prevent ugly errors in cells)

if D4 is NOT blank, then proceed

We need an average of two values, and to do that we need to look them up

 

First lookup

find D4 in the range I1:Q9, then return the result in column 9 in that row

 

Second lookup

Find E4 in the range J1:Q6, then return the result in column 9 in that row

 

Once those two lookups are returned, it takes an average of those values

 

---

 

So basically, If D4 is I2, then its one, if its not and its blank then its blank, if its not blank, and its not I2, then find two numbers in the same row as D4 in another table and average them together.

 

That's the best I can do not having seen the spreadsheet.


Edited by johnmcip, 29 July 2021 - 03:15 PM.




Share this

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users