0
$\begingroup$

I have a range of values that I input into a spreadsheet. I need the ranges to output a percentage where the result of all percentages equals 100%.

  • 18 - 5.28%
  • 3 - 31.70%
  • 14 - 6.79%
  • 12 - 7.92%
  • 4.5 - 21.13%
  • 3.5 - 27.17%

The above percentages are what I need each value to equal. I figure this out by just tinkering with number and forcing the equation to equal what I wanted it to.

But I need an equation where even if the values on the left side are changed completely ranging between 1 and 24 that the percentages on the right side still add up to 100%.

The idea is that the number 18 means it took 18 hours to sell. And because it took 18 hours to sell, I want to buy less than the one that took 3 hours to sell. I only have so much money to spend, so the total percentage of all of them cannot be more than 100% or else I will spend more than I have.

$\endgroup$
  • $\begingroup$ It looks like given numbers $a_1, \ldots, a_n$ between 0 and 24 (hrs), you want to find percentages $p_i$ that minimize some quantity like $\sum_i p_i a_i$. But I'm missing some constraint because I don't see why you don't just put 100% on the smallest item. $\endgroup$ – user326210 Aug 29 '17 at 5:55
3
$\begingroup$

Interesting question!

So you want to find inverse percentages. Note that $18$ is $6$ times greater than $3$ and $5.28%$ is $6$ times less than $31.70%$. This is the key. If you denote the percentage values by $a,b,c,d,e,f$, then: $$b=\frac{18}{3}a; c=\frac{18}{14}a; \cdots ; f=\frac{18}{3.5}a.$$ The sum of all values must be $100%$.

The formula to find the first percentage value: $$a=\frac{100}{1+\frac{18}{3}+\frac{18}{14}+\frac{18}{12}+\frac{18}{4.5}+\frac{18}{3.5}}=5.28.$$ Note: In the spreadsheet you make references to the cells with given numbers (sales hours).

$\endgroup$
  • $\begingroup$ Thank you, this worked perfectly! Equation turned out to be a lot more difficult than I thought it was. $\endgroup$ – moveit124 Aug 29 '17 at 8:45
  • $\begingroup$ You are welcome. Glad to help you. Good luck. $\endgroup$ – farruhota Aug 29 '17 at 10:04
1
$\begingroup$

If I understand correctly, your problem is: given values $a_1, \ldots, a_n$ in the range 0 to 24 (hrs), find percentages $p_1, \ldots, p_n$ such that:

  1. The percentages $p_i$ are between zero and one.
  2. The percentages sum to one. $\sum_i p_i = 1$.
  3. $p_ia_i$ has the same value for all $i$.

If we wanted to satisfy only the third constraint, we would pick some constant $C$ and define

$$\widehat p_k \equiv C/a_k.$$

To satisfy the first and second constraints, we must normalize these values. Hence, we put

$$\begin{align*}p_k &\equiv \frac{\widehat p_k}{\sum_i \widehat p_i}\\ &= \frac{C/a_k}{\sum_i C/a_i}\\&= \frac{1/a_k}{\sum_i 1/a_i}\end{align*}$$

which satisfies all three constraints.


An easier way to calculate this in a spreadsheet is this:

  1. Given your inputs $a_1, \ldots, a_n$, define their reciprocals: $b_i \equiv 1/a_i$.
  2. Compute the sum $B \equiv \sum_i b_i$.
  3. Define the percentage $p_i \equiv b_i / B$.
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.