Macro to calculate median of all data within a given year

**cameron.beyers** · 08-15-2012, 06:58 PM

Hello,

I have a macro that goes through all my data in Col A (which are dates in m/d/yyyy) and then inserts a row before all the data points whenever the year changes.....for a summary of the data

When the new year is inserted above the data points I want the adjacent cell to calculate the median for that specific year. Each row is a different parameter and will need to calculate the median for that specific year.

Here is my code so far.

Please Login or Register  to view this content.

File is attached if you have any questions.

Thanks in advance!

**JBeaucaire** · 08-16-2012, 12:55 AM

An array formula take care of this for you. Enter this formula into B11, then confirm it by pressing CTRL-SHIFT-ENTER:

=IF(B$5>0, MEDIAN(IF(YEAR($A$26:$A$45)=$A11, B$26:B$45)), "")

You will know the the array is active by the curly braces { } that appear around your formula.

Then copy that to the right, then down to the other rows with years listed in column A.

**cameron.beyers** · 08-16-2012, 11:44 AM

Thanks for the quick reply! However I am getting Values that are a little off than what they should be. I'll keep trying though. Thanks again!

**cameron.beyers** · 08-16-2012, 01:50 PM

The formula is treating all blank cells as zeros.....I can't delete these rows. Is there any way that the formula can ignore the blank cells??

---------- Post added at 12:50 PM ---------- Previous post was at 12:42 PM ----------

I figured it out. In order to ignore blank cells the formula should read.
=IF(C$5>0,MEDIAN(IF(YEAR($A$26:$A$45)=$A16,IF(C26:C45<>"",C$26:C$45))),"")

then the Ctrl+Shift+Enter for array.

Thanks a ton! This will save me lots of time!