# Ignoring Duplicates when Calculating a Median

1. ## Ignoring Duplicates when Calculating a Median

I have a spreadsheet with around 45,000 line items. I have another sheet that has calculations on them to pull the numbers shown on the attachment. A regular median calculation in excel will bring up the value of 1. I believe this is happening because of the many values of 1 in column A. If I remove the duplicates I get a value of 10.5. Is there a way to manipulate a median formula so it will ignore all duplicate values?

2. ## Re: Ignoring Duplicates when Calculating a Median

Try:

=MEDIAN(IF(FREQUENCY(A1:A49,A1:A49),A1:A49))

confirmed with CTRL+SHIFT+ENTER not just ENTER.

3. ## Re: Ignoring Duplicates when Calculating a Median

How would you enter it into the following formula:
=IFERROR(MEDIAN(IF(Data!\$H\$8:Data!\$H\$52000=\$A206,IF(Data!\$M\$8:\$M\$52000>=A\$73,IF(Data!\$M\$8:\$M\$52000<B\$73,Data!\$T\$8:\$T\$52000)))), "")

4. ## Re: Ignoring Duplicates when Calculating a Median

Try:

``Please Login or Register  to view this content.``
confirmed with CTRL+SHIFT+ENTER not just ENTER.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1