Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
I need to average the figures in several cells. However some cells have a 0
in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Lorraine, here is one way,
=SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0")) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lorraine" wrote in message ... I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine"
wrote: I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. Use this **array** formula: =AVERAGE(IF(rng<0,rng)) (substitute your range to average for 'rng'). To enter an **array** formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Thanks - this works, although Excel told me to put an extra bracket at the end.
You have saved me a lot of time. Thanks again. "Paul B" wrote: Lorraine, here is one way, =SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0")) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lorraine" wrote in message ... I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Thanks for your help- this also works. Very Clever !
"Ron Rosenfeld" wrote: On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine" wrote: I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. Use this **array** formula: =AVERAGE(IF(rng<0,rng)) (substitute your range to average for 'rng'). To enter an **array** formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Would this formula change if instead of a range of figures, several figures
from different parts of the spreadsheet were used i.e d15 + f18+ g10. Thanks. "Paul B" wrote: Lorraine, here is one way, =SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0")) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lorraine" wrote in message ... I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
I don't think that is straightforward at all. This does it but requires some
explanation =SUMPRODUCT(--(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1))<0),--(N(OFFSET(D10:G1 8,{5,8,0},{0,2,3},1,1))))/SUMPRODUCT(--(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1 ))<0)) D10:G18 is the smallest range that encompasses all 3 cells. To get D15, F18 and G10 you need row and column offsets D15 - rrow offset is 5 (15-10), column offset is 0 (D-D) F18 - rrow offset is 8 (18-10), column offset is 2 (F-D) G10 - rrow offset is 0 (10-10), column offset is 3 (G-D0 so hopefully you can see why we use the arrays {5,8,0} and {0,2,3} -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lorraine" wrote in message ... Would this formula change if instead of a range of figures, several figures from different parts of the spreadsheet were used i.e d15 + f18+ g10. Thanks. "Paul B" wrote: Lorraine, here is one way, =SUMPRODUCT((A1:A10)/COUNTIF(A1:A10,"<0")) -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Lorraine" wrote in message ... I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Actually, seeing this, we can use the same principle I gave before, but
provide a much neater solution =AVERAGE(IF(N(OFFSET(D10:G18,{5,8,0},{0,2,3},1,1)) <0,N(OFFSET(D10:G18,{5,8, 0},{0,2,3},1,1)))) still an array formula, same rationale with the embedded constants arrays. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Lorraine" wrote in message ... Thanks for your help- this also works. Very Clever ! "Ron Rosenfeld" wrote: On Thu, 22 Dec 2005 03:44:02 -0800, "Lorraine" wrote: I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula) Thanks. Use this **array** formula: =AVERAGE(IF(rng<0,rng)) (substitute your range to average for 'rng'). To enter an **array** formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Hi, Another option.. Try, =SUMPRODUCT(SUMIF(INDIRECT({"D15","F18","G10"}),"< 0",INDIRECT({"D15","F18","G10"})))/SUMPRODUCT(COUNTIF(INDIRECT({"D15","F18","G10"})," <0")) Normal enter. HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=495468 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average a set of figures which ignores 0 entries
Note that your formula does not work if any of the cells are blank, another
way would be =SUM(D15,F18,G10)/SUMPRODUCT(N(LARGE((D15,F18,G10),ROW(INDIRECT("1:" &COUNT(D 15,F18,G10))))<0)) entered normally -- Regards, Peo Sjoblom "Krishnakumar" wrote in message news:Krishnakumar.20heay_1135321881.0492@excelforu m-nospam.com... Hi, Another option.. Try, =SUMPRODUCT(SUMIF(INDIRECT({"D15","F18","G10"}),"< 0",INDIRECT({"D15","F18", "G10"})))/SUMPRODUCT(COUNTIF(INDIRECT({"D15","F18","G10"})," <0")) Normal enter. HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=495468 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Average of column entries | Excel Discussion (Misc queries) | |||
Any way to calculate an average for more than 30 entries? | Excel Worksheet Functions | |||
How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions |