Flip Percentages

[BWMagee]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]30[/TD]
[TD]70[/TD]
[TD]90[/TD]
[TD]200 [=SUM(A1:D1)][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.4[/TD]
[TD]0.3[/TD]
[TD]0.2[/TD]
[TD]0.1[/TD]
[TD]1 [=SUM(A2:D2][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Gerald Higgins]

Hi, sorry, it's not clear to me what you mean exactly ?

What do you mean by "the reverse of the value in row 2" ?
Do you mean the INVERSE ?
Which I think would be 1/0.4 for A2 ?

Or do you mean the final value in the row ?

Or something else ?

It would help if you could provide the full table including what exactly you want the results to be.

 

[BWMagee]

Thanks for the reply. Inverse sounds correct.

Eg.
20+15+10+5 = 50
Represented as percentages they are:
20/50, 15/50, 10/50, 5/50
And represented as decimals they become:
0.4, 0.3, 0.2, 0.1 = 1 (100%)

Now I want to inverse those decimals, so that the highest is the lowest and vice versa, and they still add to 1, and are correctly proportional. My expectations are that:
0.4 will become 0.1.
0.1 will become 0.4.
0.2 will become 0.3.
0.3 will becomes 0.2.

 

[Gerald Higgins]

Sorry, I still don't understand.

Same question . . .

It would help if you could provide the full table including what exactly you want the results to be.