Average except for something lower...
 

How can i do a formula that will calculate the average of a few cells as long
as the cell is over a certain amount... ??

Eg, i have

40
42
45
30

I would like the average of all of them expect any that are lower than 35

e.g if(a1:a4<35,takeaverage,ifnotexcludeanythinglower)

^^ i know that will not work but i am sure someone will know how to make it
work..



Can you help ?



Also i would also like to may a graph with the above information selecting
all the 4 cells, but would not like anything other than 3.30 on the graph...
Is this possible?

Average except for something lower...
 

Hi,

Try this array formula

=AVERAGE(IF(A1:A10<35,IF(A1:A10<"",A1:A10)))

This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike

"Clubber2010" wrote:

How can i do a formula that will calculate the average of a few cells as long
as the cell is over a certain amount... ??

Eg, i have

40
42
45
30

I would like the average of all of them expect any that are lower than 35

e.g if(a1:a4<35,takeaverage,ifnotexcludeanythinglower)

^^ i know that will not work but i am sure someone will know how to make it
work..



Can you help ?



Also i would also like to may a graph with the above information selecting
all the 4 cells, but would not like anything other than 3.30 on the graph...
Is this possible?

Average except for something lower...
 

Clubber2010;368271 Wrote:
How can i do a formula that will calculate the average of a few cells as
long
as the cell is over a certain amount... ??

Eg, i have

40
42
45
30

I would like the average of all of them expect any that are lower than
35

e.g if(a1:a4<35,takeaverage,ifnotexcludeanythinglower)

^^ i know that will not work but i am sure someone will know how to
make it
work..



Can you help ?



Also i would also like to may a graph with the above information
selecting
all the 4 cells, but would not like anything other than 3.30 on the
graph...
Is this possible?

Hi,
try =average(if(a1:a4<35,a1:a4)) and enter as an array formula with
Ctrl+Shift+Enter


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=103154

Average except for something lower...
 

Hi,

Try this non array formula solution

=sumif(A8:A11,"=35")/countif(A8:A11,"=35")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Clubber2010" wrote in message
...
How can i do a formula that will calculate the average of a few cells as
long
as the cell is over a certain amount... ??

Eg, i have

40
42
45
30

I would like the average of all of them expect any that are lower than 35

e.g if(a1:a4<35,takeaverage,ifnotexcludeanythinglower)

^^ i know that will not work but i am sure someone will know how to make
it
work..



Can you help ?



Also i would also like to may a graph with the above information selecting
all the 4 cells, but would not like anything other than 3.30 on the
graph...
Is this possible?