| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to average a column of numbers that are greater than 0?
I am trying to average a column of numbers. I want to only average the cells
that contain a number greater than 0. How do I do that? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
you can use a pivot table, or auto filter with this condititional (greater
than 0) and in this case use =subtotal(1,a2:a500) hth regards from Brazil Marcelo "JimNColorado" escreveu: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
this is an array formula that must be entered/edited using ctrl+shift+enter
=AVERAGE(IF(E1:E210,E1:E21)) -- Don Guillett SalesAid Software "JimNColorado" wrote in message ... I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
Jim =AVERAGE(IF(D2:D18<0,D2:D18)) Enter as an array Ctrl/shift/enter Don "JimNColorado" wrote in message ... I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
Jim
opps! Should be: =AVERAGE(IF(B2:B180,B2:B18)) Don "JimNColorado" wrote in message ... I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
I'll admit my bias against array formulas and offer an alternative...
Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
I support this result as the most logical....
"bpeltzer" wrote in message ... I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
That worked. Thanks. I would have never figured it out.
"bpeltzer" wrote: I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
|
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
because I don't like array functions... it is not something special... I
just dont like to use them... "Don Guillett" wrote in message ... why? -- Don Guillett SalesAid Software "Sasa Stankovic" wrote in message ... I support this result as the most logical.... "bpeltzer" wrote in message ... I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
If it's *most* logical to you, may I ask then, why would you use
Sumif(range,"0"). Seems illogical to *need* " add if it's greater then zero", *unless* there's a stipulation to exclude negative values. A simple: Sum(range)/Countif(range,"0") would be adequate without further qualification from the OP. What do you think? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Is something else going to happen "Sasa Stankovic" wrote in message ... because I don't like array functions... it is not something special... I just dont like to use them... "Don Guillett" wrote in message ... why? -- Don Guillett SalesAid Software "Sasa Stankovic" wrote in message ... I support this result as the most logical.... "bpeltzer" wrote in message ... I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
What if I want to do the same thing but I want to include negative values.
For example A1 B1 C1 D1 E1 1000 1500 -400 300 So I want to average A1 to D1 but i also want to have the formula include cell E1 because there may be times when E1 has a value - positive or negative. "Ragdyer" wrote: If it's *most* logical to you, may I ask then, why would you use Sumif(range,"0"). Seems illogical to *need* " add if it's greater then zero", *unless* there's a stipulation to exclude negative values. A simple: Sum(range)/Countif(range,"0") would be adequate without further qualification from the OP. What do you think? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Is something else going to happen "Sasa Stankovic" wrote in message ... because I don't like array functions... it is not something special... I just dont like to use them... "Don Guillett" wrote in message ... why? -- Don Guillett SalesAid Software "Sasa Stankovic" wrote in message ... I support this result as the most logical.... "bpeltzer" wrote in message ... I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
You want the average of 5 cells, not just the AVERAGE of A1:E1?
=SUM(A1:E1)/5 returns 480 AVERAGE(A1:E1) returns 600 because AVERAGE ignores blanks. Whatever suits you. Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 16:58:00 -0800, StephenAccountant wrote: What if I want to do the same thing but I want to include negative values. For example A1 B1 C1 D1 E1 1000 1500 -400 300 So I want to average A1 to D1 but i also want to have the formula include cell E1 because there may be times when E1 has a value - positive or negative. "Ragdyer" wrote: If it's *most* logical to you, may I ask then, why would you use Sumif(range,"0"). Seems illogical to *need* " add if it's greater then zero", *unless* there's a stipulation to exclude negative values. A simple: Sum(range)/Countif(range,"0") would be adequate without further qualification from the OP. What do you think? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Is something else going to happen "Sasa Stankovic" wrote in message ... because I don't like array functions... it is not something special... I just dont like to use them... "Don Guillett" wrote in message ... why? -- Don Guillett SalesAid Software "Sasa Stankovic" wrote in message ... I support this result as the most logical.... "bpeltzer" wrote in message ... I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to average a column of numbers that are greater than 0?
No what I want is for the formula to ignore all Zero values.
So if only 4 out of the 5 cells have a value - positive or negative - i want it to average the 4 cells not the 5 But if all 5 cells have values - positive or negative - i want it to average the 5 cells. "Gord Dibben" wrote: You want the average of 5 cells, not just the AVERAGE of A1:E1? =SUM(A1:E1)/5 returns 480 AVERAGE(A1:E1) returns 600 because AVERAGE ignores blanks. Whatever suits you. Gord Dibben MS Excel MVP On Mon, 10 Dec 2007 16:58:00 -0800, StephenAccountant wrote: What if I want to do the same thing but I want to include negative values. For example A1 B1 C1 D1 E1 1000 1500 -400 300 So I want to average A1 to D1 but i also want to have the formula include cell E1 because there may be times when E1 has a value - positive or negative. "Ragdyer" wrote: If it's *most* logical to you, may I ask then, why would you use Sumif(range,"0"). Seems illogical to *need* " add if it's greater then zero", *unless* there's a stipulation to exclude negative values. A simple: Sum(range)/Countif(range,"0") would be adequate without further qualification from the OP. What do you think? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Is something else going to happen "Sasa Stankovic" wrote in message ... because I don't like array functions... it is not something special... I just dont like to use them... "Don Guillett" wrote in message ... why? -- Don Guillett SalesAid Software "Sasa Stankovic" wrote in message ... I support this result as the most logical.... "bpeltzer" wrote in message ... I'll admit my bias against array formulas and offer an alternative... Average is simply sum/count. So =sumif(range,"0")/countif(range,"0") will average the positive values. "JimNColorado" wrote: I am trying to average a column of numbers. I want to only average the cells that contain a number greater than 0. How do I do that? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Typing the name and the column and getting an average (for sales people) | Excel Discussion (Misc queries) | |||
| calculate average in a column based on criteria in another column | Excel Discussion (Misc queries) | |||
| how can i multiply two columns | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Average of numbers in column between to other numbers | Excel Discussion (Misc queries) | |||
Linear Mode
