Hi all, this may be more of a math question than an Excel question, but I'm working in Excel so someone here might have the answer. Basically I'm trying to flip percentages. Eg.
<tbody>
</tbody>
Here we can see four values (row 1), and four decimals (row 2). The four decimals represent percentages, and add up to 100% [1] in E2. [For reference, we may say that they are a % of 50. So 100% = 50. A2 would be 40% of 50, etc...]
I want to multiply each of the values in row 1, by the reverse of the value in row 2. So A3 = A1 * (reverse of) A2.
The problem is that if I simply flip the values in row 2 (so that 0.4 becomes 0.6, 0.3 becomes 0.7 etc..., they no longer add up to 1 or 100%. They add up to 3 or 300%.
So my question is, how do I find the reverse of each value in row 2 (so that the highest decimal becomes lowest and lowest becomes highest), in a way that they are still correctly proportional, and still add up to 1 or 100% of 50?
Help much appreciated.
A | B | C | D | E | |
1 | 10 | 30 | 70 | 90 | 200 [=SUM(A1:D1)] |
2 | 0.4 | 0.3 | 0.2 | 0.1 | 1 [=SUM(A2:D2] |
<tbody>
</tbody>
Here we can see four values (row 1), and four decimals (row 2). The four decimals represent percentages, and add up to 100% [1] in E2. [For reference, we may say that they are a % of 50. So 100% = 50. A2 would be 40% of 50, etc...]
I want to multiply each of the values in row 1, by the reverse of the value in row 2. So A3 = A1 * (reverse of) A2.
The problem is that if I simply flip the values in row 2 (so that 0.4 becomes 0.6, 0.3 becomes 0.7 etc..., they no longer add up to 1 or 100%. They add up to 3 or 300%.
So my question is, how do I find the reverse of each value in row 2 (so that the highest decimal becomes lowest and lowest becomes highest), in a way that they are still correctly proportional, and still add up to 1 or 100% of 50?
Help much appreciated.
Last edited: