If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Average of numbers under 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 |
#2
|
|||
|
|||
Average of numbers under 10
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 |
#3
|
|||
|
|||
Average of numbers under 10
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 |
#4
|
|||
|
|||
Average of numbers under 10
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 |
#5
|
|||
|
|||
Average of numbers under 10
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 |
#6
|
|||
|
|||
Average of numbers under 10
Another one...
Array entered** : =IF(COUNTIF(K2:K10,"10"),AVERAGE(IF(K2:K10"",IF (K2:K1010,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 |
#7
|
|||
|
|||
Average of numbers under 10
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 |
Thread Tools | |
Display Modes | |
|
|