Here is an example:
A1: Value 1. A2-A30: values, or text as NA, or text as BL. A31: the mean value of A2 through A30 (the values of the cells with NA are considered as zero, the values of the cells with BL are considered as half of the value in A1, which is Value 1).
What one single formular will it be used to get the mean value in A31? Note there are multiple columns that need the same operation. So using additional columns to treat individual data and then doing the calculation is out of consideration.