| View previous topic :: View next topic |
| 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? |
|
| Back to top |
|
 |
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
Guest
|
| Posted: Wed Mar 10, 2004 2:22 pm Post subject: |
|
|
| Thank you very much. |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
|
