+ Reply to Thread
Results 1 to 14 of 14

Find Median for a given month

  1. 03-04-2015, 05:19 AM #1
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Find Median for a given month

    Hi Guys,

    Can anyone help me with finding median of count for a given month. I have tried something but this formula gives me incorrect result.

    Appreciate your help on this. please find attached sample file with data.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 03-04-2015, 06:25 AM #2
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    =MEDIAN(IF(A2:A12=H2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-1)+1,IF(D2:D12<=EOMONTH(J2,0),E2:E12)))))
    Try this array formula
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  3. 03-04-2015, 06:27 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    =MEDIAN(IF(A2:A12=H2,IF(C2:C12=K2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-1)+1,IF(D2:D12<=EOMONTH(J2,0),E2:E12))))))
    Take this array formula
    Priority was not taken in to consideration in the above formula
    Reply With QuoteRegister To Reply
  4. 03-04-2015, 06:27 AM #4
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Median for a given month

    Quote Originally Posted by nflsales View Post
    =MEDIAN(IF(A2:A12=H2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-1)+1,IF(D2:D12<=EOMONTH(J2,0),E2:E12)))))
    Try this array formula
    This gives me incorrect result.
    Reply With QuoteRegister To Reply
  5. 03-04-2015, 06:30 AM #5
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Median for a given month

    Quote Originally Posted by nflsales View Post
    =MEDIAN(IF(A2:A12=H2,IF(C2:C12=K2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-1)+1,IF(D2:D12<=EOMONTH(J2,0),E2:E12))))))
    Take this array formula
    Priority was not taken in to consideration in the above formula
    gives me result as 5 which is incorrect.
    Reply With QuoteRegister To Reply
  6. 03-04-2015, 06:36 AM #6
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    I think Your Date in Cell J2 is 01-03-2015
    So that it is calculating for March Month
    Please enter any date of Feb'15 month then it will give correct answer
    Reply With QuoteRegister To Reply
  7. 03-04-2015, 06:53 AM #7
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Median for a given month

    Quote Originally Posted by nflsales View Post
    I think Your Date in Cell J2 is 01-03-2015
    So that it is calculating for March Month
    Please enter any date of Feb'15 month then it will give correct answer
    Probably yes....but my requirement is to calculate median based on date as 1st March i.e.01/03/2015
    Reply With QuoteRegister To Reply
  8. 03-04-2015, 07:21 AM #8
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    Ok for 01/03/2015, what is the date period to look for
    and if it is 05/03/2015, what is the date period to look for
    Reply With QuoteRegister To Reply
  9. 03-04-2015, 01:11 PM #9
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Median for a given month

    It will always be first date of the month.I need to find median of previous month data.
    Reply With QuoteRegister To Reply
  10. 03-04-2015, 01:17 PM #10
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    Then try this array formula
    =MEDIAN(IF(A2:A12=H2,IF(C2:C12=K2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-2)+1,IF(D2:D12<=EOMONTH(J2,-1),E2:E12))))))
    Reply With QuoteRegister To Reply
  11. 03-05-2015, 12:24 AM #11
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Median for a given month

    Quote Originally Posted by nflsales View Post
    Then try this array formula
    =MEDIAN(IF(A2:A12=H2,IF(C2:C12=K2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-2)+1,IF(D2:D12<=EOMONTH(J2,-1),E2:E12))))))
    This works well when Class is 'AAA' i.e. single class.

    How to find median for more than 1 class?
    i.e. value of H2 is 'AAA,EEE' instead of 'AAA'
    Reply With QuoteRegister To Reply
  12. 03-05-2015, 01:12 AM #12
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    Then try below array formula
    =MEDIAN(IF(ISNUMBER(SEARCH(A2:A12,H2)),IF(C2:C12=K2,IF(B2:B12=I2,IF(D2:D12>=EOMONTH(J2,-2)+1,IF(D2:D12<=EOMONTH(J2,-1),E2:E12))))))

    But one think, it will not give correct answer if you have class like, A,AA,AAA, Since you are using search function, A,AA & AAA all 3 will match with class criteria

    If you have any problem with the above, please reattach your excel file with all possible examples
    Reply With QuoteRegister To Reply
  13. 03-05-2015, 04:12 AM #13
    linok
    linok is offline
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Median for a given month

    Thanks man!
    Reply With QuoteRegister To Reply
  14. 03-05-2015, 04:50 AM #14
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Find Median for a given month

    you are welcome
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Median Indirect: Find median in range and bring back adjacent cells
    By Keelin in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-27-2014, 08:31 AM
  2. How to calculate median per month?
    By alexjust in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2014, 05:40 PM
  3. Average and Median by week and month from daily data
    By jjj444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2013, 11:01 PM
  4. [SOLVED] Median Ifs, need to find median $ amount per deal for each year
    By xenomorph8472 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-13-2012, 02:01 PM
  5. How do find Median of a set of totals and find the relating grade
    By whitespaces in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-12-2006, 11:16 AM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1