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)



"Linda (RQ)" wrote:

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

--

Dave Peterson

On Sat, 10 Oct 2009 19:48:17 -0400, "Linda \(RQ\)"
wrote:

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



Here's one way:

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

--ron

Thanks Guys!!


"Linda (RQ)" wrote in message
...
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

Ron,

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

Thanks,
Linda


"Ron Rosenfeld" wrote in message
...
On Sat, 10 Oct 2009 19:48:17 -0400, "Linda \(RQ\)"

wrote:

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



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.

--
Biff
Microsoft Excel MVP


"Linda (RQ)" wrote in message
...
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

On Sat, 10 Oct 2009 21:09:40 -0400, "Linda RQ"
wrote:

Ron,

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

Thanks,
Linda

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