Ask Your Question
0

How to get median from dynamically picked numbers?

asked 2016-05-27 12:51:10 +0200

pronigo gravatar image

Hi,

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

This is my sheet:

image description

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2016-05-30 00:20:16 +0200

Regina gravatar image

updated 2016-05-30 08:00:57 +0200

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 Ø.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-05-27 12:51:10 +0200

Seen: 79 times

Last updated: May 30 '16