Pulling the median for certain months

CaitP

Hello,

I am trying to find the best formula that will take the values from a column for a certain month and calculate the median. My goal is to display the data as:

This month

Last month

2 months ago

3 months ago

4 months ago

5 months ago

6 months ago

I want to be able to create a run chart showing the median for each month over time. I am having no luck with Chat gpt.

VBAGuru

CaitP

Maybe I am doing something wrong. That did not work for me.

VBAGuru

Sorry realized my error. Fixing.

VBAGuru

=MEDIAN(COLLECT([Primary Column]:[Primary Column], Month:Month, IF(MONTH(TODAY()) - 1 < 1, 12 + (MONTH(TODAY()) - 1), MONTH(TODAY()) - 1)))

Not liking the error handling for when it doesn't find a month to calculate. Researching an override for that.

But works as long it can find data.

Obviously for the current month, you do not need the IF statement.

CaitP

Yes this worked! Thank you so much. I am having trouble getting it to calculate the median for the current month with this formula after removing the IF statement.

VBAGuru

Instead of the IF statement replace it with Month(Today())