This is a beautiful Monday morning for problem-solving! Here is what I am trying to achieve.

I am studying an electoral model and I want to analyse its behaviour using different settings. One of these settings is the population of sub-national entities (e.g. States in the context of the U.S.). In particular, I wish to observe the impact of population disparity, which means I want to make the State populations vary from a state where they are rather uniform to one where they are more spread out.

Since I am using Excel to compute my electoral model, I am seeking to use a slider that will determine a coefficient that is part of a formula. This formula would modify the population of my States. As the slider goes from one extreme to another, the populations would gather around the median value or expand away from it (in the latter case, the ratio between the largest to smallest State population would increase).

Let's say my input is the States' population with a wide distribution. The formula takes in a State's population, the median value of all the State populations, and the coefficient from the slider. The formula would give out a modified State population that progressively moves in closer to the median as the coefficient changes.

Depending on the design of the slider, we could have, at one end of it, the original values, and, on the other end, all values exactly equal to the median.

So I guess I want functions f and g like so:

f(x, y, a) = x * g(x, y, a)


  • x is a State population value;
  • y is the State populations' median value; and
  • a is the coefficient determined by the slider.

Any idea of how to do this?

Thanks in advance!

EDIT: Say these graphs show the number of votes per bracket (from 1 to 20) in my population. For instance, moving the slider (hence, the coefficient) would shift from the initial (wide) distribution (top graph) to a more concentrated distribution (bottom). Thus, by moving the slider, I can make the distribution denser around the median and see the impact on the electoral model. enter image description here

  • $\begingroup$ Please read the description for the tag 'distribution-theory'. $\endgroup$
    – md2perpe
    Jun 1, 2020 at 9:25

1 Answer 1


Found a good solution to my problem here (though not exactly of the proposed form).


  • a is a State's population;
  • b is the median value of the populations of all States; and
  • x is the varying coefficient (determined by the slider).

Then my formula to modify a population value is:

$$ f(x) = \frac{2 .(b-a)}{\pi} .arctan(x) + a $$

Thus: $$ f(0) = a $$ $$ \lim\limits_{x \to +\infty} f(x) = b $$

So when my coefficient/slider is at 0, my modified population is actually my initial population. But as its value increases, the modified population converges towards the median. Applying this formula to all my population values (with the same median and the same coefficient) makes them all together converge towards the median.

Thanks go to @CiaPan, via Find function that satisfies some criteria


You must log in to answer this question.