AVERAGEIF: can't get around #DIV/0! error

[dadowai]

Hi.

Forum noob here. Please be gentle.

I'm trying to get the arithmetic mean of a selection of numbers from a list of 100 values.

It's a survey of free speeds (motor traffic) and the complete list of numbers comprises 100 speed readings in kilometres per hour.

The speed limit on the road in question is 50 km/h, so I am trying to obtain (using AVERAGEIF) the mean speed of 'speeders'.

In other words, I want to calculate the average of all the readings greater than 50.

The data are in cells B24:B123, worksheet "Readings".

The reference value (in this instance 50 km/h) is in the user defined variable "speedlimit".

This is the formula I am using, and which gives the #DIV/0! error: =AVERAGEIF(Readings!B24:B123,">speedlimit")

I've tried various tweaks, but I always get the same error.

What am I doing wrong, and how can I fix it?

Thanks in advance.




EDIT: Incidentally, if I use =AVERAGEIF(Readings!B24:B123,">50") it works fine. However, I need to be able to have different values for the variable "speedlimit".

[NBVC]

Perhaps:

=AVERAGEIF(Readings!B24:B123,">"&speedlimit)

[dadowai]

Posted twice by accident. Can't delete, hence edit.

[dadowai]

Perhaps:

=AVERAGEIF(Readings!B24:B123,">"&speedlimit)

Bingo!

Worked a treat. Many thanks. Answer is 63, by the way. :-)

Now, I have to ask: why is it necessary to put > in "", and why the & before the variable speedlimit?

There is no clue at all in Excel help about these crucial details, AFAICS.

[NBVC]

because you are making a reference to a specific cell or named range, you need to keep that outside the quotes, which would make it assume the named range is a text string....

[dadowai]

The "" around the > looks very counterintuitive to me, even though it is obviously correct.

What is the significance of the & before the variable speedlimit?

[NBVC]

It's just the way these functions (SUMIF, COUNTIF, AVERAGEIF, etc) were designed... the operator and any text strings or numbers are entered between quotes... any references to cells, named ranges need to be split out from the quotes to indicate that they themselves aren't text strings, the & concatenates the operator with the criteria argument...