OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Question about AVERAGE

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
DrDew14
General User
General User


Joined: 01 Jun 2005
Posts: 8

PostPosted: Tue Jun 21, 2005 1:49 pm    Post subject: Question about AVERAGE Reply with quote

Hi all,

This is a two part question.

I am using the following formula to average the contents of some cells:

=AVERAGE(K4;Q4;W4;AC4)

1. How can I alter this formula to exclude cells with a value of 0?

2. How can I alter this formula to exclude the highest and lowest value of the four cells being averaged?

Any help would be greatly appreciated.

Nick


Last edited by DrDew14 on Wed Jun 22, 2005 6:24 am; edited 1 time in total
Back to top
View user's profile Send private message
Dale
Super User
Super User


Joined: 21 Feb 2005
Posts: 1440
Location: Australia

PostPosted: Tue Jun 21, 2005 3:33 pm    Post subject: Re: Question about AVERAGE Reply with quote

DrDew14 wrote:
Hi all,

This is a two part question.

I am using the following formula to average the contents of some cells:

=AVERAGE(K4;Q4;W4;AC4)

1. How can I alter this formula to exclude cells with a value of 0?


Somewhere else, place the following in 4 different cells:
=if (K4 = 0;"";K4)
=if (Q4 = 0;"";Q4)
=if (W4 = 0;"";W4)
=if (AC4 = 0;"";AC4)

Then average these new cells. (They can be hidden if desired)

Quote:
2. How can I alter this formula to exclude the highest and lowest value of the four cells being averaged?


Two ways to do this come to mind. The better one is to use the TRIMMEAN function with the Alpha parameter set to 0.5 - search the help index for TRIMMEAN. Play around with it a bit to see if it calculates the mean the way you need it.

The second way is to use IF statements along the lines of
=if(or(K4 = max(K4; Q4; W4; AC4);K4 = min(K4; Q4; W4; AC4));"";K4)
...duplicate for your four cells and average.

The drawback with this method is if you have two entries equal to the minimum value (or maximum value), both these values are ignored (together with its opposite). You then end up taking the mean of one value only.

Quote:
Any help would be greatly appreciated.

Nick


I hope it helps...
_________________
Dale
To err is human, but to destroy your slippers in the process takes a real son of a bitch: Me!

OOo documentation from the source
http://documentation.openoffice.org
Guides, FAQ, How Tos
Back to top
View user's profile Send private message
8daysaweek.co.uk
Super User
Super User


Joined: 29 Nov 2003
Posts: 2130
Location: UK

PostPosted: Wed Jun 22, 2005 2:15 am    Post subject: Re: Question about AVERAGE Reply with quote

Hi Nick,

I like Dale's suggestions Smile, here are some other options to think about...

DrDew14 wrote:
I am using the following formula to average the contents of some cells:

=AVERAGE(K4;Q4;W4;AC4)

1. How can I alter this formula to exclude cells with a value of 0?

Try a variation of: =SUMIF(C1:C5;">0";C1:C5)/COUNTIF(C1:C5;">0")

DrDew14 wrote:
2. How can I alter this formula to exclude the highest and lowest value of the four cells being averaged?

I thought that this "=SUMIF(C1:C5;"<>MAX(C1:C5)";C1:C5)/COUNTIF(C1:C5;"<>MAX(C1:C5)")" should work to exclude the highest value, and was going to build on that, but it doesn't (maybe a bug?) so looked for a workaround...
=SUM(SUM(C1:C5)-MAX(C1:C5)-MIN(C1:C5))/SUM(COUNT(C1:C5)-2)

HTH Smile,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
Back to top
View user's profile Send private message Visit poster's website AIM Address
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Wed Jun 22, 2005 4:34 am    Post subject: Re: Question about AVERAGE Reply with quote

DrDew14 wrote:
Hi all,

This is a two part question.

I am using the following formula to average the contents of some cells:

=AVERAGE(K4;Q4;W4;AC4)

1. How can I alter this formula to exclude cells with a value of 0?

2. How can I alter this formula to exclude the highest and lowest value of the four cells being averaged?

Any help would be greatly appreciated.

Nick


If you had values in A1:A9, use this:

=SUM(A1:A9)/COUNTIF(A1:A9;"<>0")

To exclude max and min, just subtract those, using the MAX() and MIN() functions, from the original SUM(), all enclosed in brackets, before including the /COUNTIF(A1:A9;"<>0") part.

OK, MIN() might pose a problem due to zeroes. I have to get out into the sunshine just now, but will think about it. Hopefully some kind soul will beat me to it.

David.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group