[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
presto
Guest

 Posted: Wed Mar 10, 2004 10:25 am    Post subject: Average of cells within a range that meet criteria Im having some problems getting this formula to work properly. I have a range say a1:a30 and I would like to average all cells within this range that are > 0. Ive tried: =IF(\$D\$3:\$D\$30>0;AVERAGE(\$D\$3:\$D\$30)) AND =AVERAGE(IF(\$D\$3:\$D\$30>0;\$D\$3:\$D\$30) Are these even close to being setup properly?
Guest

Posted: Wed Mar 10, 2004 10:44 am    Post subject: Re: Average of cells within a range that meet criteria

 presto wrote: Im having some problems getting this formula to work properly. I have a range say a1:a30 and I would like to average all cells within this range that are > 0. Ive tried: =IF(\$D\$3:\$D\$30>0;AVERAGE(\$D\$3:\$D\$30)) AND =AVERAGE(IF(\$D\$3:\$D\$30>0;\$D\$3:\$D\$30) Are these even close to being setup properly?

I had to de-install OOo again, so am just guessing. Also there are those who provide much neater means on the whole. However, try this:

Use SUMIF() to get the sum of all >0, and COUNTIF() ..same criterion. Then average as the SUMIF() results divided by the COUNTIF() result.

David.
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Wed Mar 10, 2004 10:59 am    Post subject: Similar to David's answer, this is how I would achieve the result you require: =SUM(A1:A30)/COUNTIF(A1:A30;">0") The IF() formula won't work with a range - that's why your formulas won't work. HTH,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
Guest

 Posted: Wed Mar 10, 2004 2:22 pm    Post subject: Thank you very much.
Guest

Posted: Wed Mar 10, 2004 2:25 pm    Post subject:

 8daysaweek.co.uk wrote: Similar to David's answer, this is how I would achieve the result you require: =SUM(A1:A30)/COUNTIF(A1:A30;">0") The IF() formula won't work with a range - that's why your formulas won't work. HTH,

Hi,

Not trying to be picky, but wouldn't plain SUM() also include any negatives if present?

David.
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

 Posted: Wed Mar 10, 2004 2:32 pm    Post subject: David, I've no problem with you being picky when I'm wrong TBH I hadn't really thought of negative numbers, posted too hastily So can we agree that your original suggestion is correct and would be entered as: =SUMIF(A1:A30;">0")/COUNTIF(A1:A30;">0") BFN ,_________________James www.8daysaweek.co.uk - A User-Focused OOo site
Guest

Posted: Wed Mar 10, 2004 7:39 pm    Post subject:

 8daysaweek.co.uk wrote: David, I've no problem with you being picky when I'm wrong TBH I hadn't really thought of negative numbers, posted too hastily So can we agree that your original suggestion is correct and would be entered as: =SUMIF(A1:A30;">0")/COUNTIF(A1:A30;">0") BFN ,

I didn't consider you "wrong", just wondered if I was expecting too much. I'm used to other spreadsheets, and would not know to have put the ">0" in quotes. Again, I don't have OOo installed, but will do that and do a bit of relearning. I'm strictly amateur in this area, just curious and interested in what others do for the most part. I'll assume oyu tried it, and it works.

David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum