View previous topic :: View next topic 
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.
Nick
Last edited by DrDew14 on Wed Jun 22, 2005 6:24 am; edited 1 time in total 

Back to top 


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 

Back to top 


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 UserFocused OOo site 

Back to top 


David Super User
Joined: 24 Oct 2003 Posts: 5668 Location: Canada

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. 

Back to top 


