Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
How do I calculate the averages of numbers in 2 sets of ranges... excluding
zero vaules? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Hi!
Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Thanks
But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Well, that's why it's a good idea to provide as much detail as possible when
posting a question. The formula I suggested only works when each range is the same size. So, instead of taking another guess at what you want: Are there any negative numbers in either range? Are there any empty cells in either range? Biff "Curtis" wrote in message ... Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
If you download and install the free add-in Morefunc.xll, you can use
the following formula... =AVERAGE(IF(SETV(ARRAY.JOIN(B9:B17,B24:B55))0,GET V())) Alternatively, try... =AVERAGE(IF(ISNA(MATCH(ROW(B9:B55)-ROW(B9)+1,{10,11,12,13,14,15},0)),IF(B 9:B550,B9:B55))) ....which will exclude from the average the 10th through 15th cell within B9:B55, relative to B9. Note that both formulas needs to be confirmed with CONTROL+SHIFT+ENTER. Also, the add-in can be download at the following link... http://xcell05.free.fr/ Hope this helps! In article , Curtis wrote: Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
You have *uneven* range sizes!
You could try this *non-array* formula if you *don't* have negative values: =SUM(B9:B17,B24:B55)/(COUNTIF(B9:B17,"0")+COUNTIF(B24:B55,"0")) If you *do* have negatives, try this *non-array* formula : =SUM(B9:B17,B24:B55)/SUM(COUNTIF(B9:B17,{"0","<0"})+COUNTIF(B24:B55,{" 0"," <0"})) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Curtis" wrote in message ... Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Sorry new to this!
Actual ranges where I need the averages are b9:b17 and b24:b53. This range can contain zero value, which I need excluded... No negative values However there is a range in a different column that des have zero and negative values where I will need to average the negative values (i.e. n9:n17 and n24: n53 "Biff" wrote: Well, that's why it's a good idea to provide as much detail as possible when posting a question. The formula I suggested only works when each range is the same size. So, instead of taking another guess at what you want: Are there any negative numbers in either range? Are there any empty cells in either range? Biff "Curtis" wrote in message ... Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Used 2nd *non-array.
Thanks Ragdyer and all "Ragdyer" wrote: You have *uneven* range sizes! You could try this *non-array* formula if you *don't* have negative values: =SUM(B9:B17,B24:B55)/(COUNTIF(B9:B17,"0")+COUNTIF(B24:B55,"0")) If you *do* have negatives, try this *non-array* formula : =SUM(B9:B17,B24:B55)/SUM(COUNTIF(B9:B17,{"0","<0"})+COUNTIF(B24:B55,{" 0"," <0"})) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Curtis" wrote in message ... Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Try Domenic's second formula. As is, it will work on your first range in
column B. You'll need to modifiy it slightly to get it to work on your other column, column N: Change: 0 To: <0 Biff "Curtis" wrote in message ... Sorry new to this! Actual ranges where I need the averages are b9:b17 and b24:b53. This range can contain zero value, which I need excluded... No negative values However there is a range in a different column that des have zero and negative values where I will need to average the negative values (i.e. n9:n17 and n24: n53 "Biff" wrote: Well, that's why it's a good idea to provide as much detail as possible when posting a question. The formula I suggested only works when each range is the same size. So, instead of taking another guess at what you want: Are there any negative numbers in either range? Are there any empty cells in either range? Biff "Curtis" wrote in message ... Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
As per JMB from another thread asking exactly the same question within a few hours......
=SUM(G7:G9,G12:G15)/(SUMPRODUCT(--(G7:G9<0))+SUMPRODUCT(--(G12:G15<0))) It is interesting that we can check for <0 when we use SUMPRODUCT, but we can't check for <0 when we use COUNTIF. This is because COUNTIF will also count null and blank. Epinn "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average if
Thanks for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Curtis" wrote in message ... Used 2nd *non-array. Thanks Ragdyer and all "Ragdyer" wrote: You have *uneven* range sizes! You could try this *non-array* formula if you *don't* have negative values: =SUM(B9:B17,B24:B55)/(COUNTIF(B9:B17,"0")+COUNTIF(B24:B55,"0")) If you *do* have negatives, try this *non-array* formula : =SUM(B9:B17,B24:B55)/SUM(COUNTIF(B9:B17,{"0","<0"})+COUNTIF(B24:B55,{" 0"," <0"})) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Curtis" wrote in message ... Thanks But when I use the formula {=AVERAGE(IF(CHOOSE({1,2},B9:B17,B24:B55)<0,CHOOS E({1,2},B9:B17,B24:B55)))} it returns a value of #N/A What am I doing wrong? "Biff" wrote: Hi! Is this what you mean? Average A1:A5 and F1:F5? If so, try this: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =AVERAGE(IF(CHOOSE({1,2},A1:A5,F1:F5)<0,CHOOSE({1 ,2},A1:A5,F1:F5))) Biff "Curtis" wrote in message ... How do I calculate the averages of numbers in 2 sets of ranges... excluding zero vaules? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
rolling average | Excel Worksheet Functions | |||
Modified Average Function | Excel Worksheet Functions | |||
omiting cells in average calcs | Excel Discussion (Misc queries) | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions |