How do you exclude cells from a formula?

[bobholda]

I need to be able to calculate the mean average of a group of cells. Most of the time those cells will have numerical values, but occasionally, the cell value will be "N/A". I want the "mean" formula to factor for those "N/A" cells by reducing the value of the divisor in the "mean" equation itself.

For example, I have the formula calculating the mean for 6 cells. When the value in each cell is "2", the mean is "2". That's good. However, as soon as I change one cell's value to "N/A", the formula calculates a new, reduced mean. I want the mean, in this case, to remain at "2", the formula only working with or recognizing cell values that are numerical.

Anyone have a suggestion?

[ken johnson]

Try...

Code: Select all

=SUMPRODUCT(AVERAGE(IF(A1:A6="N/A";"";A1:A6)))

which calculates the average of A1:A6 excluding any cells with "N/A".
Or try...

Code: Select all

=SUMPRODUCT(AVERAGE(IF(ISNUMBER(A1:A6);A1:A6;"")))

which calculates the average of only the numbers in A1:A6.

Ken Johnson

[acknak]

However, as soon as I change one cell's value to "N/A", the formula calculates a new, reduced mean.

Hmm... Something seems strange here. That's not what happens for me, and that's not the way the AVERAGE function is supposed to work.

With the formula =AVERAGE(range), any text values are ignored and only the cells with numeric values are included in the average.

Are you still using OOo 3.0.1, as shown in your signature? As far as I know, AVERAGE has always excluded text cells from the calculation, but maybe upgrading to the current version would be worthwhile anyway.

[karolus]

Hi
My guess is bobholda want exclude the errorcode #N/A not the Text→:

Code: Select all

=AVERAGE(IF(ISNA(A1:A10);"";A1:A10))

as Matrix-formula (Strg+shift+enter or with [x]Matrix-option in Functionwizard )

@Ken
What for is SUMPRODUKT in your formulas ?

Karolus

[bobholda]

Thank you all for your quick replies. The solutions offered by Ken Johnson seem to really target the exact issue I'm having. I'm glad I explained my situation clearly, at least.
However, I tried the SUMPRODUCT and AVERAGE formulas you suggested and they did not work. Here's some additional information - I'm not sure if it's relevant or not:

- I'm actually working with a table in TEXT; I am not working on a document in CALC.
- For some reason, the formula default is to use <> around cell names, as opposed to (). I don't think it likes the parenthesis, but I'm not sure.
- In TEXT, the formula list includes MEAN, MEDIAN, MODE, but not AVERAGE. Therefore, I've been using MEAN. I'm not sure if tables in TEXT recognize AVERAGE - maybe there is a limited amount of formulas that will work in a TEXT table?

Still trying...

Oh - and I'm working on a document at a work facility. I don't have the authority to upgrade the software myself, though I could request it.

Bob

[acknak]

I'm actually working with a table in TEXT; I am not working on a document in CALC.

That changes things a bit

The =MEAN function in Writer also ignores text and empty cells, but not in older versions of OOo. You'll have to upgrade to get the proper behavior.

As far as I know, that's your only choice; Writer's formula support is simply not robust enough to deal with exceptions.

I guess one alternative would be to do the calculation in OOo Calc--a spreadsheet--and then display the answer somehow in your Writer document.

[karolus]

Hi

...but the former is more natural for many users who are used to entering non-array formulas.

For me its missusing of SUMPRODUCT.

Karolus