Calculate Minimum by Ignoring NA for Non-Adjacent Cells in MS Excel

Question

I have data (example as below) where I want to find a Minimum value for L and R but ignoring NA.

I've tried to refer to https://superuser.com/questions/862279/minimum-of-non-zero-non-adjacent-cells-in-ms-excel and other functions, but it does not working.

Answer 1

You can use array formula:

if N/A is error value

{=MIN(IFERROR(IF($A$2:$J$2=K$2,$A3:$J3,""),""))}

if N/A is string just remove IFERROR function

{=MIN(IF($A$2:$J$2=K$2,$A3:$J3,""))}

Array formula after editing is confirmed by pressing ctrl + shift + enter

Answer 2

to find just values of L

=IF(AND(A11="L",NOT(A13="N/A")),A13)

and to find just values of R

=IF(AND(A11="R",NOT(A13="N/A")),A13)

and then find Minimum of them