VBA for Median calculation based off specific year

**lilybickel** · 02-23-2021, 06:30 AM

Hello everyone,
I am looking for a way to code for a median calculation in a VBA user defined function.
The end goal is to have annual averages calculated from a data set, so values included for each calculation are based on the year a value was recorded (in cell offset(0,1)). We have been able to do this for the mean calculations by using the code:

Please Login or Register  to view this content.

We also need to calculate each annual median... I am thinking along the lines of listing the values, sorting the values and outputting the middle one but have no idea how to go about coding for this?? Any ideas would be appreciated

Side note: how do I input the above code in this post as code rather than text? Sorry am new to using this forum
Thank you

**kvsrinivasamurthy** · 02-23-2021, 07:50 AM

Pl put the code in Code tags.

**kvsrinivasamurthy** · 02-23-2021, 09:58 AM

I feel it is not median it is the average of the year as your code goes.
Change these lines

Please Login or Register  to view this content.

as

Please Login or Register  to view this content.

**lilybickel** · 02-23-2021, 10:15 AM

Hi,
Sorry that was a typo the code should say +1 there. You are right this is my code for finding the mean, however I need to adapt it to also find the median and it is there that I am stuck.
Thanks

**kvsrinivasamurthy** · 02-24-2021, 01:02 AM

Pl upload a sample file.

**jindon** · 02-24-2021, 01:58 AM

UDF

Use in cell like
=GetAVR_MEDI(A2:B1000,2020)
where A2:A1000 has value to be calculated, B2:B1000 has date.

Please Login or Register  to view this content.

**kvsrinivasamurthy** · 02-24-2021, 11:17 AM

Code for Median. Result cell should be formatted for date.

Please Login or Register  to view this content.

**lilybickel** · 03-01-2021, 06:21 AM

Hello, thank you both very much for your help - sorry it has taken me so long to get back to you.
I have put both codes into an example file and kvsrinivasamurthy your code works best for what I'm after, however I need the output to be the median test score rather than the date. I have attached the file for reference now.

**kvsrinivasamurthy** · 03-01-2021, 10:01 AM

here is new code

Please Login or Register  to view this content.

**lilybickel** · 03-01-2021, 12:50 PM

Perfect, thank you so much

I have a few tests where there are multiple columns with calculations such as ratings of the test score and we are also looking to find the median of some of these column values.
The example sheet I have included shows Test score|Score rating|Date so we I would be looking to find the median score as well as the median rating based on the year of the date. Some of the tests I am working with have up to 17 columns per single instance where I am looking to calculate the median of the values in the 14th column prior to each date for example.
Would you mind explaining what the VBA code is doing so I can adapt accordingly for each test? I have tried playing around with it and can't figure it out.

**kvsrinivasamurthy** · 03-02-2021, 01:12 AM

Pl see file with codes for UDF's AnnScoreMdn and AnnRatingMdn

**lilybickel** · 03-02-2021, 11:29 AM

The code is working for the AnnScoreMdn but not for AnnRatingMdn; please see attached file (AnnScoreMdn was being used for both sections in your previous file) I can't tell what the problem is?

**kvsrinivasamurthy** · 03-03-2021, 01:16 AM

In the columns for rating formulas are there. in the formulas all numbers are written as text. Eg for 1 it is written as "1". Similarly all numbers. I have changed them as numbers.
In the columns for median for rating score median formula was used. I have corrected it.

**lilybickel** · 03-03-2021, 07:51 AM

Ah I hadn't realised that would make a difference - thank you for all your help, it is all working perfectly now

VBA for Median calculation based off specific year

LinkBack

Thread Tools

Rate This Thread

Display

VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Re: VBA for Median calculation based off specific year

Thread Information

Users Browsing this Thread

Similar Threads

If statement Median calculation returning #N/A instead of median

[SOLVED] Calculation based on the day of the year, not the date, for projecting results

[SOLVED] Using Median IF to calculate the median for a specific criteria

[SOLVED] Median Ifs, need to find median $ amount per deal for each year

[SOLVED] Is the IRR calculation based on cash flows at beginning of year?

[SOLVED] Is the IRR calculation based on cash flows at beginning of year?

[SOLVED] Is the IRR calculation based on cash flows at beginning of year?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions