Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
"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? –  Excellll Sep 22 '11 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,""))
share|improve this answer

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:

enter image description here

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

share|improve this answer
    
Great solution! Have to do exactly you said. –  Jing Sep 26 '11 at 16:17
    
Thank you very much! –  Jing Sep 26 '11 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.