SOLVED

Can't Average an Index Match function with "blank" cells

Copper Contributor

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.

 

https://docs.google.com/spreadsheets/d/1DgSVO0WzckdwSXO8BA_CvpdthSAThS3r/edit?usp=drivesdk&ouid=1160...

8 Replies

@lobo114 

Your link results in "Access Denied".

Sorry, try it now. I forgot to change the permissions.

@lobo114 

Still "Access Denied"

HansVogelaar_0-1695309009557.png

 

Sorry, rough morning! Try it again.
best response confirmed by lobo114 (Copper Contributor)
Solution

@lobo114 

Thanks. Use these. In C33:

=AVERAGE(IFERROR(INDEX('Drop Down'!F2:F6,MATCH(C8:C32,'Drop Down'!E2:E6,0)),""))

In D33:

=AVERAGE(IFERROR(INDEX('Drop Down'!H2:H5,MATCH(D8:D32,'Drop Down'!G2:G5,0)),""))

You are a Rock Star! The math checks out. Thank you for the help!

@HansVogelaar 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.

@lobo114 

See the attached version. I added a lookup range to the Drop-down sheet.

1 best response

Accepted Solutions
best response confirmed by lobo114 (Copper Contributor)
Solution

@lobo114 

Thanks. Use these. In C33:

=AVERAGE(IFERROR(INDEX('Drop Down'!F2:F6,MATCH(C8:C32,'Drop Down'!E2:E6,0)),""))

In D33:

=AVERAGE(IFERROR(INDEX('Drop Down'!H2:H5,MATCH(D8:D32,'Drop Down'!G2:G5,0)),""))

View solution in original post