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

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.

asked Sep 21, 2011 at 21:51 11 1 1 silver badge 3 3 bronze badges

"I want to treat. BL as half of the value in the second row." Did you mean the second column? If not, could you please clarify what you mean by this?

Commented Sep 22, 2011 at 0:46

3 Answers 3

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,""))

answered Sep 21, 2011 at 22:22 Sandeep Bansal Sandeep Bansal 6,412 1 1 gold badge 30 30 silver badges 34 34 bronze badges

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)

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

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.

enter image description here

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) .

answered Sep 21, 2011 at 22:59 11.1k 2 2 gold badges 40 40 silver badges 53 53 bronze badges Great solution! Have to do exactly you said. Commented Sep 26, 2011 at 16:17

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.

answered Sep 21, 2011 at 22:29 John Deters John Deters 339 2 2 silver badges 6 6 bronze badges

You must log in to answer this question.

The Overflow Blog

Hot Network Questions

Subscribe to RSS

Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

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

3 Answers 3

You must log in to answer this question.

Related

Hot Network Questions

Subscribe to RSS