Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Change from mixed caps and upper lower to all upper lower case | Excel Worksheet Functions | |||
Lower case | Excel Worksheet Functions | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
First Lower Case | Excel Worksheet Functions |