| View previous topic :: View next topic |
| Author |
Message |
Thryduulf
Newbie
Joined: 02 Mar 2012
Posts: 4
Location: London, United Kingdom
|
 Posted: Fri Mar 02, 2012 6:23 am Post subject: DMEDIAN and DMODE |
 |
|
I'm trying to find the mean, median and mode of a range of values in a database sorted by a different field. The DAVERAGE function works exactly as I want it to find the mean, but there doesn't appear to be either a DMEDIAN or DMODE function, and I can't work out how to get around this.
Specifically I need the median and mode of values in column C ("Count") where column F ("Current") contains the string "C1". the database range is A1:F495 and is sorted alphabetically on column A ("Location") (several other formulas depend on this sort order). New records are added from time to time and these can be appear at any position.
Any help would be appreciated.
Thanks
_________________
The essential things in life are seen not with the eyes, but with the heart. --Antoine de St Exupery |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Mar 02, 2012 1:59 pm Post subject: |
|
|
Define the same kind of criteria range as with the other D-funcitons and select it.
menu:Insert>Names>Define... define a named range and check the extra option "Filter"
Select the list range.
menu:Data>Define...define a named list range which includes the data list
menu:DataFilter>Advanced Filter...
Pick the name of the criteria range from the list box.
Choose "Copy Output" in the extra options and specify some target range such as OtherSheet.A1
Run the functions against the filtered output range.
When the data range has been modified, call Data>Refresh
When the criteria have changed call Data>Filter>Advanced... [criteria Range]
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
Thryduulf
Newbie
Joined: 02 Mar 2012
Posts: 4
Location: London, United Kingdom
|
| Posted: Sat Mar 03, 2012 12:38 pm Post subject: |
|
|
Thank you for that, I've never done anything with advanced filtering so I'll have play with the test and get my head around it.
_________________
The essential things in life are seen not with the eyes, but with the heart. --Antoine de St Exupery |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Mar 04, 2012 10:27 am Post subject: |
|
|
You may also test the following array formulas with my example sheet. As far as I can see they return the same results as the normal formulas with filtered subsets.
| Code: | =MODE(IF(($A$3:$A$201>=$J$7)*($A$3:$A$201<=$J$8)*($B$3:$B$201=$J$6);$C$3:$C$201;""))
=MEDIAN(IF(($A$3:$A$201>=$J$7)*($A$3:$A$201<=$J$8)*($B$3:$B$201=$J$6);$C$3:$C$201;""))
|
Finish the input of an array formula with Ctrl+Shift+Enter rather than Enter.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Mar 04, 2012 12:00 pm Post subject: |
|
|
| Villeroy wrote: | You may also test the following array formulas with my example sheet. As far as I can see they return the same results as the normal formulas with filtered subsets (edit: except for the median which can not ignore the gaps in the array)
| Code: | =MODE(IF(($A$3:$A$201>=$J$7)*($A$3:$A$201<=$J$8)*($B$3:$B$201=$J$6);$C$3:$C$201;""))
=MEDIAN(IF(($A$3:$A$201>=$J$7)*($A$3:$A$201<=$J$8)*($B$3:$B$201=$J$6);$C$3:$C$201;""))
|
Finish the input of an array formula with Ctrl+Shift+Enter rather than Enter. |
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
