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

DMEDIAN and DMODE

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


Joined: 02 Mar 2012
Posts: 4
Location: London, United Kingdom

PostPosted: Fri Mar 02, 2012 6:23 am    Post subject: DMEDIAN and DMODE Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Mar 02, 2012 1:59 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Mar 03, 2012 10:48 am    Post subject: Reply with quote

Example file with semi-automatic filter criteria:
http://www.mediafire.com/file/i1p2brwgnzscmfe/AdvancedFiltering.ods

Edit the source data and call Data>Refresh to apply the same criteria against the modified data
Call Data>Filter>Advanced and hit OK to re-read the filter criteria.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Thryduulf
Newbie
Newbie


Joined: 02 Mar 2012
Posts: 4
Location: London, United Kingdom

PostPosted: Sat Mar 03, 2012 12:38 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Mar 04, 2012 10:27 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Mar 04, 2012 12:00 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
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