• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MEDIAN=Find the extra number, to get the desired result.

bines53

Active Member
Hello friends,

There is a table with 8 numbers, numbers range from 1 to 8 = A3: A10
Now, I want to add a number in cell A2, there will be 9 numbers, but does not change the result 4, cell D6.
I want to get all the possible results, which, 8,7,6,5,4.

Thank you!
 

Attachments

  • test.median.xlsx
    7.8 KB · Views: 11
Hi David ,

Is the value in D6 variable ? If so , how is it going to vary ?

How will the values between A3 and A10 vary , if at all they are going to vary ?

As it stands , any value less than 3 will change the median value in D6 ; no value which is greater than 3 will ever change the median value in D6.

So what are you really looking for ?

Narayan
 
Hi Narayan,

D6 variable ,can vary each time the number of high or low at 1.
values between A3 and A10 ,only one number varies,

David ,
 
Hi Narayan,

What I'm trying to do, to predict the change in the market price the next day.
I have the stock exchange in the last 9 trading days. To predict the change, I take only eight trading days, plus what I think it is, according to statistical tool, the median.
A2, the expected rate of change from what I've defined in D6.

David ,
 
Are you aware that the value in D6 is not 4? It is 3.5. The cell format has just been set to display as Number with 0 decimal places.

Regards
 
@bines53

I guess you need to understand the little mathematics behind median. When you calculate it, you first sort the data in ascending order and look for the middle value, for odd nos you can easily find it, when there are even in count, you take average of the two that are in middle. For your particular case the list is like below


Code:
Unsorted Sorted
1 1
3 3
3 3
8 3
4 4
7 5
3 7
5 8

For this case, the values are even, hence you will average the 4th and 5th value to get 3.5.


Now the question is what to add in order to get the same median. since this was the case of even numbers, you can simply add 3.5 as when it will be added, it will convert the total number of count to odd (there will 9 #s to sort) and the middle value will be 3.5 like this:

Code:
1
3
3
3
3.5
4
5
7
8

Now for case where initially you have odd number of values, adding a value will lead you to average two values, Lets assume that following is the data set under consideration:

Code:
Unsroted        Sorted
1        1
3        3
3        3
4        3
7        4
3        5
5        7

Now since you want average of two numbers to be the same first number [(X1+X2)/2 should equal X1], you simply need to add 3 here [3 will be X2], adding 3 will make the 4th and the 5th digit to be 3 when sorted and have will give you same median.

Hope that helps!
 
Hi @XOR LX ,ֲ@Faseeh,

I should clarify that issue,
The median I work, is 9 lines (9 numbers),
Cell A2, it will be filled the next day, but before that, I want to test statistically.
I know the median with range even numbers will not be an integer.
D6, my assumption would be 4

Regards,
 
Last edited:
@bines53,

with 9 rows of data, it is the case of even number when you will add the 10th. so simply average the 5th and 6th element of the sorted data. I am also attaching a sample file.
 

Attachments

  • test.median (1).xlsx
    10.1 KB · Views: 2
Hi Faseeh,

The idea is, what is the ninth number (from 1 to 8), to give the result, 4.
In this example, there are 5 options, 8,7,6,5,4.
I need a formula, such as AGGREGATE function, give me, all possible options.


Regards,
 
can u explain that how we can get median of 4 by adding 8 as 9th digit. Sorry for not picking up ur idea.
 
Hi Faseeh,

If we add the digit 8, ninth number ,Then we get
1
3
3
3
4
5
7
8
8

The middle number, ie, the number 4.

Regards,
 
This is what i told you earlier from my previous posts!

Now the question is what to add in order to get the same median. since this was the case of even numbers, you can simply add 3.5 as when it will be added, it will convert the total number of count to odd (there will 9 #s to sort) and the middle value will be 3.5 like this:

..In median The Position of a record counts. It will make no difference if you add 5,6,7 instead of 8.

Bottom Line is to add the median value for 8 records to get the same median with 9 records.
 
Hi Faseeh,

Let's take a series of numbers,
1
2
2
3
6
6
7
7
With the addition of the ninth number,
We can get 4 different results,
Number equal to or greater than 6, we get a 6 (median).
Number equal to or less than 3, we get a 3 (median).
No. 4, we get a 4 (median).
No. 5, we get a 5 (median).

Regards
 
With the addition of the ninth number,
We can get 4 different results,
Number equal to or greater than 6, we get a 6 (median).
Number equal to or less than 3, we get a 3 (median).
No. 4, we get a 4 (median).
No. 5, we get a 5 (median).

Initially you asked for a digit that will make no change to your median. Off course replacing any thing after 4 will make no difference. i guess you are missing some sought of link or aspect of the actual problem.


For me it will make difference if you take the mean of the data, or if you calculate range of the data or if you calculate the standard deviation for it. so if you question really stops at the point that what should be the 9th number to keep median same you can go with any of the number greater then 4, but if you really want logical ground for your selection or if you want the median to truly represent your data, you should also consider mean, standard deviation and range that it adds to your data.

Please see attachment, pros and cons of your selection
 

Attachments

  • Book1.xlsx
    10.4 KB · Views: 1
Last edited:
Hi Faseeh,

Thank you for your help !

But, if I have 8 numbers, what is the formula that will give me the 9th number, when I set the median I want to examine.
I understand, as a series of numbers, when there is a difference of 1, between the 4th and 5th numbers,there will not be a problem.
Beyond that it is necessary to a complex formula.

Regards,
 
Hi.

Can you just clarify? You want a formula which will return all possible values y which, given a series of n values:

a1, a2, a3, ..., an

with median x, will result in a series of n+1 values:

y, a1, a2, a3, ..., an

also with median x?

Regards
 
Dear Bines,

Can you give me a solution to the set of data with working logic for it. I can try, and hopefully provide you with a formula. Not much details, just an expected answer and its working logic for the result.
 
@XOR LX ,@Faseeh

In response #14,
There is an example of a series of numbers, the formula,
if I want the median will be 3, the answer is, these numbers: 1,2,3.
If I want the median will be 6, the answer is, these numbers: 6,7,8.
If I want the median will be 4, the answer is, these numbers: 4
If I want the median will be 5, the answer is, these numbers: 5
If this is not possible, I'll try to think of logic, as proposed Faseeh

Regards,
 
So, denoting the entries in your original list, containing N integers, where N is even, by:

a1,a2,a3,...,aN

let A be the set of integers containing all possible values for a further entry, a(N+1), such that the median of the N+1 entries:

a1,a2,a3,...,aN,a(N+1)

is x.

Then:
  • If x<a(N/2) or x>a(N/2+1) then A={}
  • If x=a(N/2) then A={1,2,3,...,a(N/2)}
  • If x=a(N/2+1) then A={a(N/2+1),a(N/2+2),a(N/2+3),...}
  • Otherwise, A={x}
Regards
 
Last edited:
Using your example:

1,2,2,3,6,6,7,7

Here, N=8, so:

a(N/2) (the value in the 4th position of the sorted list) is 3
a(N/2+1) (the value in the 5th position of the sorted list) is 6

If x is your choice of median for this list plus an additional entry, then the possible choices for that additional entry will be defined by:
  • If x<3 or x>6 then no additional values exist
  • If x=3 then the additional value can be either 1, 2 or 3
  • If x=6 then the additional value can be either 6, 7 or 8
  • If x=4 then the only possible choice for the additional value is 4
  • If x=5 then the only possible choice for the additional value is 5
Or, to put my logic another way:

First, find the middle two values in the original sorted list: let's call the lower of these MiddleLower and the upper MiddleUpper.

  • If your choice of desired median for the new list is smaller than MiddleLower or greater than MiddleUpper then there are no possible additional values which would result in your desired median
  • If your choice of desired median for the new list is equal to MiddleLower then the possibilities for the additional value which would result in your desired median are 1, 2, 3, ..., etc. up to MiddleLower
  • If your choice of desired median for the new list is equal to MiddleUpper then the possibilities for the additional value which would result in your desired median are from MiddleUpper up to, well, infinity
  • If your choice of desired median for the new list is between MiddleLower and MiddleUpper then the only possibility for the additional value which would result in your desired median is that which is identical to your choice of desired median

I trust you are able to produce the necessary Excel formulas from these statements?

Regards
 
Hi XOR LX,

Now, I understand that.
Are you suggesting to me that the solution is possible and can be done.
Looks tomorrow, if I can this challenge.

Thank you!

David
 
Knowing your Excel skills, I have no doubt that you're more than capable of translating the logical statements I've supplied into the formulaic equivalents in Excel.

Regards
 
Hello friends,

Here's my solution,

The table is not sorted, the formula a bit long, but easy to understand,
I believe that you can shorten it,
C1=desired median
E1=The formula.

Thank all the helpers!

David
 

Attachments

  • median.test.xlsx
    7.9 KB · Views: 0
Back
Top