Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average IF
I am using MS Excel 2003.
I am trying to get the average of numbers in a column if the fit several criteria. SUMPRODUCT is not appropriate, right? I tried this AS AN ARRAY but it doesn't seem to work. E2744 is a date =IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"") Ideas. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average IF
Hi,
Try this array formula, I shortened the ranges for debugging =AVERAGE(IF(F2:F27E27,IF(H2:H270,IF(L2:L270,H2: H27)))) 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 "PAL" wrote: I am using MS Excel 2003. I am trying to get the average of numbers in a column if the fit several criteria. SUMPRODUCT is not appropriate, right? I tried this AS AN ARRAY but it doesn't seem to work. E2744 is a date =IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"") Ideas. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average IF
Try this array formula** :
=AVERAGE(IF(($F$2:$F$2741E2744)*($H$2:$H$27410)* ($L$2:$L$27410),$H$2:$H$2741)) ** 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 "PAL" wrote in message ... I am using MS Excel 2003. I am trying to get the average of numbers in a column if the fit several criteria. SUMPRODUCT is not appropriate, right? I tried this AS AN ARRAY but it doesn't seem to work. E2744 is a date =IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"") Ideas. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average IF
You can use SUMPRODUCT if you don't want to use arrays; just have to remember
how an average is actually calculated and break it down. =SUMPRODUCT(($F$2:$F$2741E2744)*($H$2:$H$27410)* ($L$2:$L$27410)*($H$2:$H$2741))/SUMPRODUCT(($F$2:$F$2741E2744)*($H$2:$H$27410)*( $L$2:$L$27410)) This calculates the sum of everything that meets your crtieria, then divides by the count. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "PAL" wrote: I am using MS Excel 2003. I am trying to get the average of numbers in a column if the fit several criteria. SUMPRODUCT is not appropriate, right? I tried this AS AN ARRAY but it doesn't seem to work. E2744 is a date =IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"") Ideas. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average IF
Hi,
Based on the fact that E2744 is not absolute then you are probably not copying this formula so you can remove the absolute references: =AVERAGE(IF((F2:F2741B1)*(H2:H2741*L2:L27410),H2 :H2741)) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PAL" wrote: I am using MS Excel 2003. I am trying to get the average of numbers in a column if the fit several criteria. SUMPRODUCT is not appropriate, right? I tried this AS AN ARRAY but it doesn't seem to work. E2744 is a date =IF(AND($F$2:$F$2741E2744,$H$2:$H$27410,$L$2:$L$ 27410),AVERAGE($H$2:$H$2741),"") Ideas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |