Mac Numbers 3.6.1: Averageif Function

In your formula where you have

<160

try replacing that with

"<"&160

SG

Hi Wai Hung Tony,

The bugaboo here is that condition statements must be presented as a text string. The function descriptions in the browser and in Numbers Help (and in the Formulas and functions User Guide which applied to the earlier Numbers '09) never come right out and say that, but the examples given with each of the XXXIF and XXXIFS functions show it in every case where a comparison operator is used.

The second example in your initial post, "=SUMIF(Height (cm), 170, Mass (kg))", shows the only exception: if the operator is omitted, the default comparison is "equal to," and the function will recognize a number as a valid comparison value.

Replacing <160 with "<"&160 (as suggested in SG's post above), or with "<160" as shown in Apple's examples makes the comparison statement a text string, and avoids the error.

You mention "AVERAGEIF" in your question, which leads me to assume you are using SUMIF as a step toward calculating the average of the summed masses.

Since AVERAGEIF will give you that result without the need to also use COUNTIF, you might want to use it, as I've done in the example below.

Formula shown for E4: AVERAGEIF(B,">=160",C)

Formula in E3: AVERAGEIF(B,"<160",C)

Note the inclusion of "=" in one of the formulas. If this were not done, neither 'average' figure would include any students whose height was recorded as 160 cm.

Regards,

Barry

The condition statement can be entered as "just a number." For example, if you wanted to count how many times 50 occurs in column B you could just do:

=COUNTIF(B,50)

But when you have a comparison operator such as < or <=, etc. you need to wrap it in " " and concatenate the condition with &, as in the example in my post above.

SG