Average of numbers under 10

  • Thread starter Thread starter Linda \(RQ\)
  • Start date Start date
L

Linda \(RQ\)

Hi,

I am using Excel 2003. On my sheet named "Original" in column K I have a
list of numbers they range from .1 to 50 but could go higher. On another
sheet I have this function =AVERAGE(Original!K2:K10) in a cell which give me
the average of these numbers. I would like to make another cell with the
same function but I want to it to only average items in column K that are
less than 10 and ignore any that are 10 or more.

Thanks,
Linda
 
You can use =sumif()/countif()

=sumif(original!k2:k10,"<"&10) / countif(original!k2:k10,"<"&10)
 
Hi,

I am using Excel 2003. On my sheet named "Original" in column K I have a
list of numbers they range from .1 to 50 but could go higher. On another
sheet I have this function =AVERAGE(Original!K2:K10) in a cell which give me
the average of these numbers. I would like to make another cell with the
same function but I want to it to only average items in column K that are
less than 10 and ignore any that are 10 or more.

Thanks,
Linda
Click to expand...

Here's one way:

=SUMIF(Original!$K$2:$K$10,"<10")/COUNTIF(Original!$K$2:$K$10,"<10")

--ron
 
Another one...

Array entered** :

=IF(COUNTIF(K2:K10,"<10"),AVERAGE(IF(K2:K10<>"",IF(K2:K10<10,K2:K10))),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
Ron,

What do the $ do/mean? Your results were the same as Daves.

Thanks,
Linda
Click to expand...

Check HELP for "Addressing" or Absolute Address.
Excel has several modes of addressing cells: Absolute, Relative, and mixed.

If you drag Dave's formula to another cell, the cell references will change.

If you drag a formula with absolute addresses to another cell, cell references
preceded by the "$" will not change.

This can be useful if you want a reference to a data table to remain constant,
while the reference to a lookup value changes.

In this particular case, since the "<10" is not a cell reference, it won't make
any difference.

But if the "<10" was replaced by a cell reference; e.g. "<"&M7 and you had a
series of values in M7:M9 that you wished to evaluate against, you could enter
a formula in N7 and fill down to N9; the table reference would remain the same,
and the "lookup reference" would adjust.
--ron
 
Back
Top