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

Author Message
DrDew14
General User

Joined: 01 Jun 2005
Posts: 8

 Posted: Tue Jun 21, 2005 1:49 pm    Post subject: Question about AVERAGE 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. NickLast edited by DrDew14 on Wed Jun 22, 2005 6:24 am; edited 1 time in total
Dale
Super User

Joined: 21 Feb 2005
Posts: 1440
Location: Australia

Posted: Tue Jun 21, 2005 3:33 pm    Post subject: Re: Question about AVERAGE

 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
8daysaweek.co.uk
Super User

Joined: 29 Nov 2003
Posts: 2130
Location: UK

Posted: Wed Jun 22, 2005 2:15 am    Post subject: Re: Question about AVERAGE

Hi Nick,

I like Dale's suggestions , 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 ,
_________________
James
www.8daysaweek.co.uk - A User-Focused OOo site
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Wed Jun 22, 2005 4:34 am    Post subject: Re: Question about AVERAGE

 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.
 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