Need a percentage based on inputs

Question

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.

Answer 1

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

Answer 2

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