Excel : inverse proportional distribution Solved

Question

Hello,
I am struggling with a seemingly simple problem that I would like to address in Excel.
I need to distribute a global bonus of 5000 euros to 20 employees based on an inverse proportional allocation to a reference value.
Here are the 20 values.
817.00
477.00
567.00
537.00
537.00
329.00
567.00
567.00
396.00
477.00
467.00
517.00
537.00
477.00
567.00
477.00
421.00
517.00
477.00
Can someone help me solve this problem in a simple and elegant way?
I would like to thank everyone who is willing to tackle this issue.

Configuration: Windows XP Internet Explorer 6.0 Excel 2002

nono · Accepted Answer

Inverse Amount Amount Resulting Coefficient To distribute 329 =$A$21*1/A2 =B2/$B$21 =$D$22*C2 396 =$A$21*1/A3 =B3/$B$21 =$D$22*C3 421 =$A$21*1/A4 =B4/$B$21 =$D$22*C4 467 =$A$21*1/A5 =B5/$B$21 =$D$22*C5 477 =$A$21*1/A6 =B6/$B$21 =$D$22*C6 477 =$A$21*1/A7 =B7/$B$21 =$D$22*C7 477 =$A$21*1/A8 =B8/$B$21 =$D$22*C8 477 =$A$21*1/A9 =B9/$B$21 =$D$22*C9 477 =$A$21*1/A10 =B10/$B$21 =$D$22*C10 517 =$A$21*1/A11 =B11/$B$21 =$D$22*C11 517 =$A$21*1/A12 =B12/$B$21 =$D$22*C12 537 =$A$21*1/A13 =B13/$B$21 =$D$22*C13 537 =$A$21*1/A14 =B14/$B$21 =$D$22*C14 537 =$A$21*1/A15 =B15/$B$21 =$D$22*C15 567 =$A$21*1/A16 =B16/$B$21 =$D$22*C16 567 =$A$21*1/A17 =B17/$B$21 =$D$22*C17 567 =$A$21*1/A18 =B18/$B$21 =$D$22*C18 567 =$A$21*1/A19 =B19/$B$21 =$D$22*C19 817 =$A$21*1/A20 =B20/$B$21 =$D$22*C20 =SUM(A2:A20) =SUM(B2:B20) =SUM(C2:C20) =SUM(D2:D20) 5000

eriiic · Answer

Good evening,

If you enter the coefficients in column A, with your total coefficients equal to 9728:
B1: =ROUND(5000*A1/9728,2)

and you drag the formula down.
With the rounding it comes to 5000.04

eric

jedi69 · Answer

Hello!

1° you only give 19 values

2° if I understand correctly, you want to distribute more to the least important value (proportionally to the others) in order to "maintain" the proportion of the base values?

hmcirta · Answer

Hello

I have a little tip to suggest

After calculating PROPORTIONALLY as eriiic proposed, you select the column of bonuses calculated that way and you click "copy", and while keeping the same selection, you click on the small arrow of the paste button and choose "paste values" (don't change the selection before pasting!).
Then you select the column of coefficients (values) and sort them in ascending order A-Z (in the window that appears select continue with the current selection) then in the same way you sort the column of bonuses, but this time you sort in the reverse order Z-A (i.e., descending). This way you will get the inversely proportional bonus for each value.
I hope this tip is elegant enough to answer the question lol.

--

What is the use of knowledge if it is not shared

eriiic · Answer

So in inverse proportion: B1: 1/A1 to copy down to B20 B21: =SUM(B1:B20) C1: =ROUND(5000*B1/$B$21,2) to copy down to B20  eric

jedi69 · Answer

Well, I must say there are some math whizzes tonight!!   Congratulations to you!

jemite · Answer

Thank you all for your contributions that helped me solve my problem.
I didn't expect to spark so much "enthusiasm" at such an hour and with such a topic.
It's really nice.
I will remember it.
I think I will regularly check if I can help poor internet users in difficulty, based on my limited knowledge.
Catch you later.

Excel : inverse proportional distribution

7 réponses

Texture issues in enshrouded

Canon mb5350 - cartridge replacement impossible

Football manager (club management game)

Select next line csh foreach

Win32 kamso then rootkit detection

Win32:spyware-gen [trj] detected by avast

Unknown video error on the freebox player

Make it or break it

How to open a cbr file

Please log in with manager privileges.