Calc CountIf Alternative/Work Around

users
#1

All:

In the spreadsheet I'm working on, I'm trying to find the number of
items that are more than 6 standard deviations from the norm.

Standard deviation formula is StDev(Slant.B38:Slant.Slant.B1038))
That formula works.

The obvious solution, as expressed in the formula
COUNTIF(Slant.B38:Slant.B1038,>IQ_Slant.B44) does not work.
Error # 510: Missing Variable.

Using quotation marks for the criteria to search by (">IQ_Slant.B44")
does not work.

Is there a way to count the number of cells whose value is greater than
the variable, when the variable is the value found in a specific cell?

jonathon

#2

Yup. Your syntax is almost right, but not quite. You need:
COUNTIF(Slant.B38:Slant.B1038;">"&IQ_Slant.B44)

You can no doubt see that COUNTIF() expects an inequality to be expressed as a text string. So ">1234" will work, but not ">A1", since that "A1" will not be seen as a cell reference. If you need to incorporate a cell reference, you need to concatenate the ">" sign with the value in the cell, which will automatically be converted to text because of the context of the reference.

I trust this helps.

Brian Barker

#3

Yup. Your syntax is almost right, but not quite. You need:
COUNTIF(Slant.B38:Slant.B1038;">"&IQ_Slant.B44)

Thanks. That worked.

You can no doubt see that COUNTIF() expects an inequality to be
expressed as a text string. So ">1234" will work, but not ">A1", since
that "A1" will not be seen as a cell reference. If you need to
incorporate a cell reference, you need to concatenate the ">" sign with
the value in the cell, which will automatically be converted to text
because of the context of the reference.

This looks like one of those basic things that I should have known, but
somehow or other missed. :frowning:

Now wondering how many other things I've missed/skipped over, without
realizing it.

jonathon