Median of Even Set; How to Capture them?

Hi everyone,

Say I have a dat set such as:

4
5
6
7

The median is (5+6)/2=5.5!

However, I am not interested in the answer here, but the 5 and the 6!!!

Is there an excel function or way to do so?

Thanks alot,
Mike

0
7/27/2005 9:06:18 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
636 Views

Similar Articles

[PageSpeed] 35

Do you want the output in one single cell or in 2 separate cells?


Mike Wrote: 
> Hi everyone,
> 
> Say I have a dat set such as:
> 
> 4
> 5
> 6
> 7
> 
> The median is (5+6)/2=5.5!
> 
> However, I am not interested in the answer here, but the 5 and the
> 6!!!
> 
> Is there an excel function or way to do so?
> 
> Thanks alot,
> Mike


-- 
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=7094
View this thread: http://www.excelforum.com/showthread.php?threadid=390763

0
7/27/2005 10:11:19 PM
Here is one solution to find "the Reversed Median".

Value One: 
=IF(ISODD(COUNT(A1:A6)),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+1)/2),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6))/2))

Value Two: 
=IF(ISODD(COUNT(A1:A6)),"",INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+2)/2))

Example:
1, 2, 2, 4, 5, 6 --> 2 and 4 
1, 2,    ,4, 5, 6 --> 4

Hope it helped
Ola Sandstr�m


Attached zip-file
http://www.excelforum.com/attachment.php?attachmentid=3648&stc=

+-------------------------------------------------------------------
|Filename: Book5.zip                                                
|Download: http://www.excelforum.com/attachment.php?postid=3648     
+-------------------------------------------------------------------

--
olas
-----------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1776
View this thread: http://www.excelforum.com/showthread.php?threadid=39076

0
7/27/2005 11:30:38 PM
Morrigan,

I want them in TWO different cells........thanks,

Mike

0
7/28/2005 10:44:45 AM
Olasa,

Tried your formula, but none of them did work!

Have you tried them on an example to see if they are working as they
appear above?

Mike

0
7/28/2005 11:12:52 AM
Those are awfully complex formulas for a simple calculation.  Why not
   =LARGE(A1:A6,COUNT(A1:A6)/2)
for the median (if n odd) or the larger middle value (if n even), and
   =IF(ISEVEN(COUNT($A$1:$A$6)),SMALL($A$1:$A$6,COUNT($A$1:$A$6)/2),"")
for the smaller middle value (if n even)

Jerry

olasa wrote:

> Here is one solution to find "the Reversed Median".
> 
> Value One: 
> =IF(ISODD(COUNT(A1:A6)),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+1)/2),INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6))/2))
> 
> Value Two: 
> =IF(ISODD(COUNT(A1:A6)),"",INDEX(LARGE(A1:A6,ROW(INDIRECT("1:"&COUNT(A1:A6)))),(COUNT(A1:A6)+2)/2))
> 
> Example:
> 1, 2, 2, 4, 5, 6 --> 2 and 4 
> 1, 2,    ,4, 5, 6 --> 4
> 
> Hope it helped
> Ola Sandstr�m

0
post_a_reply (1395)
7/28/2005 12:18:33 PM
Jerry,

I agree, your 1st formula is working fine but the 2nd one is NOT!?

Could you please check it?

Thanks,
Mike

0
7/28/2005 12:22:47 PM
Jerry,

Yours are BOTH working now.....thank you ALL

Thanks alot,
Mike

0
7/28/2005 12:24:50 PM
Reply:

Similar Artilces:

Set Customer F7 Issues
I'm trying to setup customer id cards The cards would have a bar code and cashier would scan them. I have two problems i'm running into. 1. When cashier presses F7 and scans the card unless the focus is on "ACCOUNT" the software can select wrong customer. Also if the a item bar code is scanned or a wrong card is scanned t will select some account instead of prompting the cashier . The focus can be changed if manual customer look up by name is used . Is there a way to prompt the cashier if the customers card( customer number) is not found. Is there a way to bl...

.ics file adds appointment even when there is a conflict
I've a .net application that creates .ics file and send over email. Recepient (Outlook 2003) is able to open that file and add appointment to their calendar. Issue is that the appointment is added even when the recepient has another event scheduled during that time. How can the recepient get an alert if there is a conflict with existing event during that time. ...

Setting A Number Equal to Another Number
I was wondering if I could set a cell with one number equal to anothe number in another cell. I am having a hard time trying to explain wha I mean so I try to use an example. For example i have the number .93 in cell B3. I want the number i cell C3 to depend on the number in B3. For example if the number i between .90 and 1 I want the number in C3 to be 4, if the number i between .80 and .90 I want the number in C3 to be 3, and so on. Th number in cell B3 does change, so I want the number in C3 to chang based on the change of B3. Any help is greatly appreciated -- slag01 --------------...

How do I save toolbar setting on new mail?
I am trying to make two toolbars only take up one line instead of two, but when I change it on a new outgoing mail it is not saved. it's caused by an toolbar added by a 3rd party utility. Get rid of the culprit and the toolbars will 'stick'. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchang...

Dynamics.SET
GP should have the facility to change the .SET file settings depending on the company a user logged into. In this way different report formats, or forms layouts could be utilised for each company automatically. Currently, this can be done by having different icons pointing to specific dynamics.set and dictionary files. (Logged from case no 8330010) ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, f...

not collating/stapling sets within one print job
Mary Sauer answered my previous question. Her explanation and link referred to Publisher 2000, 2002 and 2003. Would an upgrade to 2007 eliminate this issue? ...

Getting MEDIAN and PERCENTILE to exclude #ERROR values
Is there a way to get PERCENTILE or MEDIAN to exclude error values? I give as an argument a range in which some of the values are #VALUE, but rest are valid, legal numeric values, and it is those remaining non-error values that i want MEDIAN or PERCENTILE of. Thank you. Try the following formulas, which need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =MEDIAN(IF(ISNUMBER(A2:A100),A2:A100)) and =PERCENTILE(IF(ISNUMBER(A2:A100),A2:A100),0.8) Adjust the references and percentile value accordingly. Hope this helps! In article <UTqfg.2573$hv1.1679@trnddc01>, &...

POP3 account set up help!
Can anybody please provide me with the correct information? I am trying to set up Outlook so that I can receive my Hotmail email. What is the correct "incoming mail server (POP3)?" and what is the correct "outgoing mail server (SMTP)?" Help please! That isn't officially supported by Microsoft. You're unlikely to get an answer for that question. It's not hard to configure in Outlook Express (which comes with IE) though it is a little buggy. But to answer your question, the POP and SMTP servers are actually lengthy URLs that seem to change periodically. ...

Unicode setting question
Hi How can I change VS2008 VC project settings if I do not want to use UNICODE with L"string" for many functions . Is this newsgroup the right one to ask this question ? Thank for your teaching On 5=BF=F929=C0=CF, =BF=C0=C8=C42=BD=C331=BA=D0, Kid <K...@discussions.micro= soft.com> wrote: > Hi > > How can I change VS2008 VC project settings if I do not want to use > UNICODE with L"string" for many functions . > > Is this newsgroup the right one to ask this question ? > > Thank for your teaching project property - confiuration propertie...

Infinite Applying User Settings
There is an application that installs just fine on most of our Windows XP Pro SP2 workstations. It is called ClearCase. (Please no comments about ClearCase being good or bad. I'm stuck with it.) On a few workstations, however, ClearCase will install apparently successfully and then tell us that a reboot is required to finish up. The reboot makes it to the "Applying User Settings" message and hangs. We've given it the opportunity to apply them for many hours (overnight). The only recourse is to power down and try booting again. (Unfortunately we worker-bees ar...

Setting up an account
I have a windows live hotmail account. Can someone please tell me what settings I need to put in the boxs Windows Mail cannot access any http email accounts. However, you can access your Hotmail account via POP3. The required settings are here... http://mailcall.spaces.live.com/Blog/cns!CC9301187A51FE33!49799.entry You need to use SSL, and authentication. I'd also suggest using port 587 for SMTP. (The newer Windows Live Mail can access Hotmail/Live http accounts.) -- Dave N. MS-MVP (Mail) Windows 7 Ultimate http://download.live.com/wlmail "Linda"...

Setting CPU Affinity
Is it possible to set CPU affinity on hyperthreaded cores from VB6? I can set real cores but not Intel HyperThreded ones, there must be something extra/different I need to do? Hints, Tips, URLs appreciated! Ed ...

Quick-Access-Toolbar Settings: SAVE on other PC ?
HOW to save my Quick-Access-Toolbar SETTINGS ? Best on a stick, to work with on an other PC? Thanks. If the QAT settings are in your normal.dotm, save it under a new name. Save the file to the Word/Startup folder that is along the user path for your user name on that computer. It will load whenever you start Word. You can save the QAT dotm in other locations, but you will have to load it yourself every time you start Word. Most of the experts here recommend that you _not_ load the add-on template from a "stick". Pam jsyh wrote: >HOW to save my Quick-Access...

Outlook insists sending a message even when outbox is empty
Receiving error message "Can not send ...(0x800CCC0D)" but the outbox is EMPTY! I have several email accounts and reading them all but sending out only thru one account. I suspect that there is an internal message trying to acknowledge that I have read a message. How can I delete it? kajtzu60 <kajtzu60@discussions.microsoft.com> wrote: > Receiving error message "Can not send ...(0x800CCC0D)" but the outbox > is EMPTY! I have several email accounts and reading them all but > sending out only thru one account. I suspect that there is an > internal messa...

Text or Sub-report on even pages of a report
I would like to print "a standard text message" on even numbered pages of a report. This is akin to printing "disclaimers" on the back side of two-sided printing. I can see a way to do this using the "Page Footer" format event. Not having tried this before, can you offer any advice? Hi, you can put following code in page footer format event: Me.lblDisc.Visible = (Me.Page \ 2 = Me.Page / 2) where lblDisc is a label control with text message -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com ...

Set report criteria in code?
Is it possible to set a reports criteria in code? I am trying to use Stephen Lebans report-to-pdf tool, which uses the report as a parameter. I need to specify a criteria for the report, like I do when I open the report for printing: DoCmd.OpenReport strReportName, acViewNormal, , strReportParam I don't see how to set report criteria in the report-to-pdf tool, but if I can set the criteria before I use the tool, that should work. Thanks in advance for any help. Fred I haven't used Stephens solution but you can always use a little DAO code to dynamically change...

Median question
Hi Guys, I am working on a median formula as follows =MEDIAN(IF(OR(Data!$D$2:$D$29998=MCalcs!$A$11,Data!$D$2:$D$29998=$A$12),(IF(Data!$F$2:$F$29998>=MCalcs!B$7,(IF(Data!$F$2:$F$29998<=MCalcs!B$8,Data!$E$2:$E$29998))))))I have 10 product codes with monthly sales data, The worksheet Data whichstores my sales data, MCalcs is my median calulations worksheet. Thespreadsheet is pretty big storing 3 years worth of sales data, I willprobably go up to 5 years in total.What I am trying to do is work out a median of a group of numbers based onif it matches 2 product codes using this part of the ...

Cannot access a database file even after splitting
Hi All, I am working on Access 2003 . I have created a database file and saved it on the network for users to access. I split the file and placed the mdb and be file on the network. i keep getting this error and when i view my file on the network 2 more files with the same name and a lock icon are opened. That implies that some user is using the file but i want to view it too. How can i bypass this error and what does this mean. Could not lock file. (Error 3050) The operating system will not allow Microsoft® Jet to create a lock file (.LDB) in the same directory where the database...

How do I set up Outlook 2007?
I don't know how to set up Outlook 2007. I click on it, and the wizard comes up, but when I enter my hotmail information, nothing happens; the wizard never finishes installing the software and I have to start over again. I do not know very much about computer software or computers in general. Any help would be much appreciated. Thank you. pcrup wrote: > I don't know how to set up Outlook 2007. I click on it, and the wizard comes > up, but when I enter my hotmail information, nothing happens; the wizard > never finishes installing the software and I have to start over a...

CRM 3.0
When installing 3.0, you specify the outbound e-mail settings (xch server, smtp settings, etc). Anyone know where the CRM stashes this information? I'd like to be able to modify it without having to repair the CRM installation. Thanks in advance. Dave in the registry on the crm web server hkey_local_machine/software/microsoft/mscrm ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Dave Ireland" <direland@salentica.com> wrote in message news:OPRBx5ELGHA.984@tk2msftngp13.phx.gbl... > When installing 3.0, yo...

3 questions about ActiveX settings?
I'm using WinXP-pro and Outlook2003. When I see a certain (reputable) eNewsletter every day in the long list of incoming emails, I click on this one to make it full sized so I can read the headlines. But before Outlook brings it up, it warns... (yellow exclamation point)"Your current security settings prohibit running ActiveX controls on this page. As a result, the page may not display correctly" It used to not do this, two things have happened recently...first, I've downloaded all the Win patches, and secondly, the newscompany seemd to have upgraded their newslett...

An even EASIER question HELP!
I have one simple question that I'd like help with. How do I copy the contents of a ROW and paste them into a COLUMN? for example: 1 | 2 | 3 | 4 | 5 into: 1 - 2 - 3 - 4 - 5 thx! Ian ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Copy your cells. Select your top destination cell (can't be in the copied range). Choose Edit/Paste Special and check the Transpose checkbox. In article <yaunus.u7wwy@excelforum-nospam.com>, yaunus <yaunus.u7wwy@...

Setting Conditions in a Cell
Hello everyone. I have limited a cell (i.e F51) to these conditions when users key in data in it. a) All letters are Upper Case b) Maximum number of letter is 3 Bob Philips has helped me with this formula for that :- =AND(LEN(F51)=3,NOT(ISNUMBER(--LEFT(F51,1))),NOT(ISNUMBER(--MID (F51,2,1))),NOT(ISNUMBER(--RIGHT(F51,1))),EXACT(F51,UPPER(F51))) It works perfectly. And now, I would like to set one more condition in that cell F51. In cell F51, the data should not be the same with any data (no duplication/repetition) in cells B110:B241. It works with this formula =COUNTIF...

smtp server settings are getting changed
Somehow my smtp server settings are being changed in Outlook 2003 every time I reboot my computer. It is changing from my ISP's smtp server setting (for example mail.domainname.com) to localhost. Is this a virus or spyware attack trying to use me as an smtp relay or hopefully something more benign that someone else has experienced and knows how to fix? Hopefully it is the latter. Thanks. It is your anti-virus changing it. Disable the AV plug-in - it is useless and a major pain. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to ...

Calculation for Median
Please help me understand how to get this to work. I opened a new module and named it modMedianCalc. Here is my SQL Public Function MedianCalc(tbl_EstVsActual As String, Median As String) As Single Dim MedianDB As DAO.Database Dim ssMedian As DAO.Recordset Dim RCount As Integer, i As Integer, x As Double, y As Double, _ OffSet As Integer Set MedianDB = CurrentDb() Set ssMedian = MedianDB.OpenRecordset("SELECT [" & Median & _ "] FROM [" & tbl_EstVsActual & "] WHERE [" & Median & _ ...