Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of a , < range
Does anyone know how to average a range of numbers based on a criteria, where the numbers have to fall within a range of data? I have tried combining a number of statements and nothing is working. I want to average a column of numbers that will have data added to it every day, but throw out anything over 4 and under 14. -- Deb Pingel ------------------------------------------------------------------------ Deb Pingel's Profile: http://www.excelforum.com/member.php...o&userid=20119 View this thread: http://www.excelforum.com/showthread...hreadid=495354 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of a , < range
=SUMPRODUCT((A2:A100<4)+(A2:A10014),A2:A100)/SUMPRODUCT(((A2:A100<4)+(A2:A1 0014))*(ISNUMBER(A2:A100))) or =AVERAGE(IF(((A2:A100<4)+(A2:A10014))*(ISNUMBER(A 2:A100)),A2:A100)) the latter entered with ctrl + shift & enter replace A2:A100 with your range (do not use the whole range A:A since the formula won't work with that, use a range size you pretty much know won't be larger like maybe A2:A1000 or something) -- Regards, Peo Sjoblom "Deb Pingel" wrote in message ... Does anyone know how to average a range of numbers based on a criteria, where the numbers have to fall within a range of data? I have tried combining a number of statements and nothing is working. I want to average a column of numbers that will have data added to it every day, but throw out anything over 4 and under 14. -- Deb Pingel ------------------------------------------------------------------------ Deb Pingel's Profile: http://www.excelforum.com/member.php...o&userid=20119 View this thread: http://www.excelforum.com/showthread...hreadid=495354 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of a , < range
Hi,
You may try the following array formula (Ctrl+Shift+Enter) =average(if((rangeA)*(range<B)),range)) Hope this helps. Regards, Ashish Mathur "Deb Pingel" wrote: Does anyone know how to average a range of numbers based on a criteria, where the numbers have to fall within a range of data? I have tried combining a number of statements and nothing is working. I want to average a column of numbers that will have data added to it every day, but throw out anything over 4 and under 14. -- Deb Pingel ------------------------------------------------------------------------ Deb Pingel's Profile: http://www.excelforum.com/member.php...o&userid=20119 View this thread: http://www.excelforum.com/showthread...hreadid=495354 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of a , < range
She wanted to exclude values between 4 and 14, that means she wants the
average of values less than or equal to 4 (or less than 4 depending on her thinking) and values greater than or equal to 14 (or greater than 14), the multiplication you use won't work since it works as AND and how can an AND function be both <=4 AND =14? This would need the equivalent of the OR function which in an array would be the plus sign -- Regards, Peo Sjoblom "Ashish Mathur" wrote in message ... Hi, You may try the following array formula (Ctrl+Shift+Enter) =average(if((rangeA)*(range<B)),range)) Hope this helps. Regards, Ashish Mathur "Deb Pingel" wrote: Does anyone know how to average a range of numbers based on a criteria, where the numbers have to fall within a range of data? I have tried combining a number of statements and nothing is working. I want to average a column of numbers that will have data added to it every day, but throw out anything over 4 and under 14. -- Deb Pingel ------------------------------------------------------------------------ Deb Pingel's Profile: http://www.excelforum.com/member.php...o&userid=20119 View this thread: http://www.excelforum.com/showthread...hreadid=495354 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of a , < range
On Wed, 21 Dec 2005 15:54:33 -0600, Deb Pingel
wrote: Does anyone know how to average a range of numbers based on a criteria, where the numbers have to fall within a range of data? I have tried combining a number of statements and nothing is working. I want to average a column of numbers that will have data added to it every day, but throw out anything over 4 and under 14. If I understand what you write, you want to AVERAGE everything that is less than or equal to 4; as well as everything that is greater than or equal to 14. So try: =(SUMIF(RNG,"<=4") + SUMIF(RNG,"=14")) / (COUNTIF(RNG,"<=4")+COUNTIF(RNG,"=14")) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation range | Excel Discussion (Misc queries) | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Match function...random search? | Excel Worksheet Functions | |||
formula to calculate the average of a range basing on condition | Excel Worksheet Functions | |||
average of visible cells in a filtered range | Excel Worksheet Functions |