How to automatically treat the text in a cell as 0, or as half of the value in another cell?

Question

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.

Answer 1

You've actually confused it a lot here, if I get what you mean then you have:

Cells A1 - A30 With the values of ND or BL

In B1 - B30 you have values

IN D1 - D30 you want to calculate the values of the rows.

If so you can go to D1 and enter:

=IF(A1="ND","0",IF(A1="BL", B1/2,""))

Answer 2

Enter this in A31. Adjust it to fit with other columns:

=SUM(A2:A30,0*(A2:A30="NA"),(A1/2)*(A2:A30="BL"))/ROWS(A2:A30)

Where:

A2:A30 contains numbers, "NA" or "BL".
A1 is Value 1

What it does:

Adds values in cells A2 through A30 -- treating "NA" as 0 and "BL" as half of A1 -- and then divides the sum by the number of items in A2 through A30. Essentially, that's how averages/means are computed.

After typing the formula, press Ctrl + Shift + Enter to commit it.

Sample:

Notes:

The formula above treats blank cells as 0 and includes them in the computation.

If you want to ignore blank cells in computing for the average, change ROWS(A2:A30) in the formula to COUNTA(A2:A30).

Answer 3

I don't understand what you mean by "in the second row". Do you mean "half the value in the row above the current row" or "half the value in the row below the current row?" Or do you mean "half the value in cell A2?"

I interpreted your "second row" to mean the value in "the row below". Anyway, the answer would be similar, with an adjustment to where the IF statement finds its multiplicand. I used "nested IF" statements to test for your two constants.

I put values in column A, and put this formula in column B:

=IF($A1="BL",0.5*$A2,IF($A1="ND",0,$A1))
=IF($A2="BL",0.5*$A3,IF($A2="ND",0,$A2))

...

I then took the average of column B.