+ Reply to Thread
Results 1 to 6 of 6

calculating MEDIAN with conditions, VBA

  1. #1
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    calculating MEDIAN with conditions, VBA

    Hello,

    I have realized that it is easy to use the averageifs in VBA to calculate the average, but when it comes to median, it is a bit tricky.

    Could someone point me in the right direction as to how to go about implementing it?

    Please see the attached file. There are two conditions that need to be applied on median.

    condition 1) users will choose a specific period to calculate, and 2) the record has to meet the analyzable condition, which is just "Yes"

    so in the cell formula bar, I would use this:
    Please Login or Register  to view this content.
    But to translate it into VBA, I'm stuck.

    Has anybody has any idea?

    Thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: calculating MEDIAN with conditions, VBA

    Maybe another approach is this: I recorded a macro and just copied the macro directly into cell range in VBA, as shown below, but I got a compile error when I click on the button.

    Please Login or Register  to view this content.
    The recorded macro is based on the cell formula in H5 of worksheet "Test"

    Anybody has any idea?
    Attached Files Attached Files

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: calculating MEDIAN with conditions, VBA

    Try this
    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: calculating MEDIAN with conditions, VBA

    Quote Originally Posted by nilem View Post
    Try this
    Please Login or Register  to view this content.
    Hi nilem,

    it worked out well. Thank you.

    I will let you know if I run into additional problems.

  5. #5
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: calculating MEDIAN with conditions, VBA

    Actually Nilem,

    How do you represent the entire column using R1C1 formualArray in VBA? I'm trying to do a median calculation on the entire column.

    Basically, more records will be added in on a on-going basis.

    I have realized that your version of the code only takes care of range from A2:A6, which needs to be expanding.

    Thanks
    Attached Files Attached Files
    Last edited by Lifeseeker; 01-05-2012 at 07:28 PM. Reason: Do median on entire column

  6. #6
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: calculating MEDIAN with conditions, VBA

    Formulas are recalculated a long time, if you use the entire column. So I suggest this option:
    Please Login or Register  to view this content.
    Try adding more information on the sheet 'A', and run this code.
    Unfortunately failed to attach a file.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1