| 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 User-Focused 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 |
|
|
|
