Forum Discussion

Copper Contributor

Jul 16, 2021

Solved

Excel - Risk Rating calculation using "If(or)" function

I have copied this formula - =@IF(OR($M10="",$L10=""),"",INDEX(Matrix_Rating,MATCH($M10,Matrix_Likelihood,0),VALUE(LEFT($L10))))

from one Excel spreadsheet to another to perform the same Risk Rating task, however it does not work on the second Excel spreadsheet. I have checked to see if the original sheet has a reference page but it doesn't. So I am confused as to why this formula works on one Excel spreadsheet and not the other. Can you help me please.

Copy of WRAC JB.xlsx811 KB

Formulas and Functions

Yea_So
Jul 18, 2021
Jblake998
This is the workbook that i sent on my response, and it works. Isn't it the right value?

16 Replies

Yea_So
Bronze Contributor
Jul 16, 2021
Jblake998

Here's your problem:
The one that says #REF
I don't see Matrix_Rating defined there too
- Jblake998
  Copper Contributor
  Jul 17, 2021
  Yea_So
  
  Yes your right. It must refer to that "Risk_Matrix" in another document.
  So I guess I'll have to do it using an inbuilt table in this document.
  
  Thanks for your help.
  - Yea_So
    Bronze Contributor
    Jul 17, 2021
    Jblake998
    
    You can define that range in the Risk_Matrix Sheet as a named range "Matrix_Rating and your formula should work as is.