I have a table that contains user selected data validation drop downs for Probability and Severity. My formula assigns those selected words a number from another sheet using an Index Match function and then averages those numbers at the bottom of the table. My formula works fine until a user selects "N/A" from column B, which doesn't require them to then use the drop down in columns C or D which results in an #N/A error at the bottom of the columns. My formula that I am using for column C is: =AVERAGE(INDEX('Drop Down'!F2:F6,MATCH(C8:C32,'Drop Down'!E2:E6,0))). I'm guessing the reason that I am getting the error at the bottom of the column is that my current formula sees data in the blank C and D cells due to the data validation drop downs and can't complete the calculation. Is there a way I can alter my formula, so it ignores these "blank" cells when it calculates? My thought was to write the formula to say if there was an "N/A" in column B, don't include the column C cell in the average calculation. Any help would be greatly appreciated. Here is the link to my spreadsheet.
@Hans Vogelaar I'm trying to update the formula you helped me with above to produce a word by referencing the number that the current formula calculates. In my attached example, I would like to combine the formula in cell C35 with the formula in cell C33. The end result would be cell C33 producing a word instead of the number like it currently does. Currently, cell C33 assigns a number value to the words in cells C8:C32 and then averages that value in C33. I would like to go a step further and take that number and assign it back to a word. I can't figure out how to combine these two functions into a new function.
