# How to get median from dynamically picked numbers?

Hi,

I can't find in google answer to my problem. Hope you guys will help me!

This is my sheet:

This is shooting result. You can see here columns with points from 1 to 10 and number of shoots to each region of target.

And now I have to get median to get mean value of one shoot. In this example it should be something like this:

=MEDIAN(10;10;10;9;9;9;9;9;8;8)

Because we've 3x10, 5x9 and 2x8 shoots. How can I do that automatically?

Hope you guys understand my problem!

edit retag close merge delete

Sort by » oldest newest most voted

I would use another sheet to make the calculations. There you can split the calculation in smaller parts. Such way the formulas are easier to understand and maintaining the sheets is much easier.

My idea is to cumulation the values. Then I can look up which shooting result corresponds to the middle index. There are some +1 tweaks, because the MATCH functions results in the previous category in case there is no exact match.

Median.ods [2016-05-30 a shorter solution now]

Are you sure, you are looking for the median? The arithmetic mean is much easier, see the column labeled with Ø.

more