Average function with formulas

[deegadams]

Can someone help me, I need to average a column's results. How can I average the results of a formula in a column and ignore negative numbers? I can't use the average function because I have a formula in the cells.

 

[Matt Rogers]

Hi.

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' >10</td><td align='right' >10</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' >10</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' >-100</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='right' >10</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='right' >10</td><td align='right' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>=AVERAGEIF</Span><Span style='color:#0000DD'>(A1:A10,">0",A1:A10)</Span><Span style='color:#222222'></Span></td></tr></table>

 

[Matt Rogers]

Or Excelversion <2007

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Arial; color:#000000; background-color:#FFFFFF; font-size:10px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#cacaca'><td> </td><td align='middle'>A</td><td align='middle'>B</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' >10</td><td align='right' >10</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' >10</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='right' >-100</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='right' >10</td><td align='right' > </td></tr><tr><td style='background-color:#cacaca' align='middle'>5</td><td align='right' >10</td><td align='right' > </td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Arial; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td><Span style='color:#222222'>{=AVERAGE</Span><Span style='color:#0000DD'>(IF</Span><Span style='color:#222222'>(A1:A10>0,A1:A10)</Span><Span style='color:#0000DD'>)</Span><Span style='color:#222222'>}</Span></td></tr></table><table style='font-family:Arial; font-size:8pt; background-color:#FFFFFF'><tr><td style='font-weight:bold'>Array-Formula!</td></tr><tr><td><span>Ctrl+Shift + Enter </span><span style='text-decoration:underline'></span><span></span></td></tr><tr><td><span></span><span style='font-weight:bold; color:#FF0000'></span><span></span></td></tr></table>