[excel 2007] omgekeerd evenredige verdeling

[Xorsist]

Ik heb een rij met percentages en hierbij wil ik eigenlijk de inverse zien.

10	40
20	30
30	20
40	10

Bovenstaand is zeer simpel voorgesteld, mijn data bestaat uit percentages.

2,95%	?
4,03%	?
6,42%	?
21,71%	?
25,18%	?
39,70%	?

Hier wil ik dus net als bovenstaand de inverse van hebben. Het gaat hier om de verdeling van kosten waarbij degene met de hoogste % bijdrage in de linkerkolom het minste bij hoeft te dragen in de rechterkolom.

Ik krijg de formule niet rond wellicht dat hier ieman een idee heeft.

[Fish]

Huiswerk ?

Je neemt elke keer de som van van je kolom en je trekt daar de waarde van het individuele veld af ?

nee kan niet

[ Voor 9% gewijzigd door Fish op 03-01-2012 23:34 ]

[Xorsist]

Die tijd heb ik al even gehad, heb me werk wel naar huis meegenomen ;-)


Daar heb ik ook aan zitten denken maar dan kom ik aan de 500%. Ik vindt het niet erg maar ik ben bang dat de bobo's boven me het daar niet voor doen ;-)

[Verwijderd]

@fish, ik denk niet dat het zo eenvoudig is, je zit dan al snel boven 100%

maar geef zelf eens het voorbeeld van het resultaat voor de percentagetabel.

ik vermoed volgend formule voor excel nl, op voorwaarde dat de percentages in stijgende volgorde gesorteerd zijn

code:

=INDEX($A$1:$A$6;RANG(A1;$A$1:$A$6))

start in cel B1

[MAX3400]

x% van iets moet natuurlijk wel vergeleken worden met een absolute waarde. Of je moet de percentages eerst met elkaar vergelijken, daar een inverse op toepassen en dan daar weer een percentage van berekenen.

Lijkt een beetje op Externe iPad/iPhone helpdesk(semi-werk) Tenzij je meer/duidelijker info geeft over waar de percentages precies over gaan etcetera, kunnen we gokken tot we een ons wegen?

[Xorsist]

Gokken doen we in een casino en daar zijn we hier niet op GOT

De percentages welke ik weergegeven heb komen voort uit verkopen welke door bepaalde afdelingen gemaakt zijn. Nu is er een verlies binnen de organisatie en moet dat verlies omgekeerd evenredig verdeeld worden naar die afdelingen.

Dus er is 250.000 te verdelen waarbij degene die het minste bijgedragen heeft aan de omzetcijfers (de 2,95% in mijn voorbeeld) de hoogste "boete" toebedeeld krijgt. Dit alles naar inverse rato van de bijdrage aan de omzetcijfers.

[MAX3400]

Ah, maar dan moet je de eerste kolom gaan uitdrukken ten opzichte van elkaar, daar de inverse van nemen en dat maal 250K doen.

Voorbeeld: 2,95% gaat 12 maal in 39.7... Dat betekent dat 2.95 moet worden vervangen door 12 en de 39.7 door 1

De 4.03 wordt dan 9.nogwat van 39 etc..

Daarna tel je kolom 1 bij elkaar op. Deelt 250K door het totaal, uit mijn hoofd wordt 1 eenheid dan rond de 8000 ofzo waardoor de boete voor 2.95 dus 12x8000 wordt, voor 4.03 9x8000 etc.

[ Voor 24% gewijzigd door MAX3400 op 03-01-2012 23:51 ]

[Xorsist]

Dit begint er inderdaad op te lijken, ik begrijp wat je bedoeld alleen ik raak je kwijt bij de inversie (sorrie was vanochtend 5 uur uit bed).

[Fish]

Wil je het ondersteboven zien. als in je voorbeeld

of wil je een verhoudingsgeweize toevoeging zien waarvan het totaal 100% is

[B_FORCE]

Is dit niet gewoon een lijst sorteren in Excel?

http://www.google.nl/search?q=sort+list+excel

[Xorsist]

Gaat om de verhouding.

MAX3400: heb het geheel doorgerekend (was zelf gelijk doorgegaan naar percentages) maar wat me op valt is dat in mijn simpele voorbeeld als ik beide kolommen optel ik telkens aan hetzelfde getal kom (50)

Doe ik hetzelfde bij mijn getallen dan krijg ik een U vormige elipse. Dit kan heel goed te maken hebben met de data welke ik heb (bovenstaande percentages zijn werkelijke getallen).

Vooralsnog lijkt het er op dat ik heb wat ik zoek, bedankt zo ver, ga er morgen als ik weer wat frisser ben even verder naar kijken.

@B_force was het maar zo simpel dan had ik echt geen topic geopend

[B_FORCE]

Dan moet je toch iets duidelijker zijn

Naar ik begrijp heb je links een kolom met percentages staan van laag naar hoog.
Rechts staan de kosten die met dat specifieke percentage correspondeert.
Uiteraard precies omgekeerd, dus van hoog naar laag.

Wat ik van je verhaal begrijp, is dat je die rij (met specifiek bedrag?) wil hersorteren (inverteren in dit geval)?

Overigens is je linker kolom met percentages niet compleet, ik kom niet ui op 100%
(ergens afrondingsfout denk ik)

edit:
Sorry, ik heb over een stukje heen gelezen.
Wat jij wil doen is: (100 - percentage)/100 * bedrag = boete
Op die manier is krijgt groep met kleinste percentage grootste deel van 250000
Dus voor 2,95%

(100 - 2,95) / 100 *250000 = 242625

en bijvoorbeeld 25.18%:
(100 - 39.7) / 100 * 250000 = 150750

(je kunt natuurlijk ook maal 2500 doen, maar dat maakt de formule minder inzichtelijk voor anderen)

edit 2:
Wacht, dat gaat natuurlijk niet goed, totaal bedrag kan immers niet meer dan 250000 zijn.
Moment geduld aub .....

[ Voor 36% gewijzigd door B_FORCE op 04-01-2012 00:34 ]

[Fish]

dit heb ik nu staan

data in kolom a
formule dan doorkopieren waarbij a1 de referentie is waar de formule tegen staat

=(SUM(A:A)-(A1*$D$9))/(COUNTA(A:A)-($D$9))

d9 is een verdelingsterkte factor....
Hier kun je een "boete" relatief milder of stenger maken 0<d9<(aantal entries-1) normaal stel ik hem 1


maar ja het is meer aan jullie hoe hard je het wil beboeten en wil verdelen uiteindelijk. samen ben je een organisatie. en zou je zelf de verdeelsleutel of verhouding moeten bepalen

[ Voor 77% gewijzigd door Fish op 04-01-2012 01:10 ]

[Lustucru]

Of die verdeling rechtvaardig uitpakt is vers2 idd, maar de berekening is als volgt:
Je neemt de reciproke van de bijdrage aan de winst. Die tellen idd niet tot 100 op, dus dat reken je terug naar een verhouding door dat te delen door de som van alle reciproke waarden. Je krijgt dan keurig een reeks getallen die zich omgekeerd verhouden als de originelen, dwz als A 3x meer bijdroeg aan de omzet dan B, dan wordt B 3x zo hard gekort.

Stel je data staan in A1:A10. Je kunt dan in kolom B =1/A1 etc doen, in B11 totaliseren en in C1..cC10 =B1/$B$11. Of in één keer als matrixformule:

code:

{=(1/A1)/SOM(1/$A$1:$A$10)}

Bevestigen met ctr-shift-enter en naar beneden trekken.

[ Voor 5% gewijzigd door Lustucru op 04-01-2012 01:13 ]

[Fish]

Uiteindelijk werk je met de verschillen in de verlieskant dus bij 3 * is er 97% niet waargemaakt
en bij 40% is 60% niet waargemaakt. dus dan is dit wat er bij mij uitvalt. die verhouding klopt wel aardig (factor op 1)

2.95		19.408	48520.00
4.03		19.192	47980.00
6.42		18.714	46785.00
21.71		15.656	39140.00
25.18		14.962	37405.00
39.7		12.058	30145.00

en toch klinkt het krom

nou ja kijk maar hoe je het als bedrijf dat wil versleutelen

[ Voor 16% gewijzigd door Fish op 04-01-2012 01:22 ]

[B_FORCE]

Ja, daar kwam ik nu uiteindelijk ook op uit. (iets andere formule vorm, maar is in feite zelfde )
Het wordt zo behoorlijk gemiddeld, waardoor het idd niet helemaal lijkt te kloppen.

edit:
Volgens mij zit de denkfout in het volgende.
Stel je zou op 0% komen (of 0% naderen), dan betekent dat volgend het idee van Xorsist, dat deze groep 250000 als "boete" krijgt.
Met de voorbeelden die ik hier zie, krijg je dat niet als uitkomst. (je komt maar tot 50000)

[ Voor 43% gewijzigd door B_FORCE op 04-01-2012 01:41 ]

[Lustucru]

Ik kom iig op iets anders:

2,95	39,70%	99251,35
4,03	29,06%	72652,97
6,42	18,24%	45606,15
21,71	5,39%	13486,48
25,18	4,65%	11627,94
39,70	2,95%	7375,10
99,99	1	250000,00

Maar idd reciproke hebben zo hun nadelen. een afdeling die 0 heeft bijgedragen krijgt de volle lading voor zijn kiezen, of beter gezegd: een oneindige boete.

[Mental]

Ik kom op hetzelfde uit ja.
Komt neer op per afdeling het volgende doen:

100% (afrondingsfout hierboven wegdenken) / percentage = tussengetal.

Dit voor alle afdelingen doen, dan weet je hoe vaak het percentage van de afdeling in het totaalpercentage past en hoe 'zwaar' dat percentage weegt ten opzicht van de andere afdelingen doe je als volgt:

Tel alle tussengetallen bij elkaar op en deel het tussengetal van de afdeling door de som van alle tussengetallen, daar komt er percentage uit en als je die allemaal bij elkaar optelt kom je weer netjes op 100%.

In tabelvorm:



Zoals hierboven al gezegd kan deze formule niet omgaan met een 0 omzet.
Het tussengetal heeft in de wiskunde ook een naam maar ik kan er niet opkomen.

edit: of je leest even de volledige berichten

[ Voor 3% gewijzigd door Mental op 04-01-2012 02:33 ]

[Xorsist]

Heren bedankt voor het meedenken. De tabel zoals deze door Mental en Lustucru weergegeven wordt is wat ik zelf gisteravond ook al had en na nogmaals doorrekenen kon ik er niets anders van maken.

[Lustucru]

Ik vraag me alleen wel af of die verdeling ook redelijk is, of wat die percentages voorstellen?

Als het zes gelijkwaardige afdelingen zouden zijn, zouden ze allemaal 16,6% hebben moeten bijdragen aan de omzet. Dan is het voor de afdelingen 3-6 best cru om boven verwachting te presteren en toch gekort te worden. Of andersom: stel dat afdeling 1 een of ander rotprodukt in de markt moet zetten en vooraf werd er niet meer dan 1% bijdrage verwacht. Dan presteren ze 3x beter dan verwacht maar krijgen wel de volle mep; terwijl een andere afdeling misschien onderpresteert (er was bv 45% verwacht en ze leveren slechts 39,7%) en gespaard blijft. Of te wel: wellicht moet je de prestatie eerst afzetten tegen de verwachting?

[Mental]

Het verhaal eromheen is ook meer om de opdracht wat makkelijker te begrijpen denk ik; het gaat gewoon om het toepassen van een formule.

[Xorsist]

Okay om aan de intresse en vraagstellingen te voldoen bij deze een wat uitgebreider verhaal.

Ik werk bij een vereniging van verenigingen welke opgezet is om de productie van aangesloten verenigingen te realiseren. Iedere vereniging heeft zijn eigen verkoper/verkoopkanaal en de een brengt meer in als de ander ter productie. De ene vereniging kan 2 leden hebben en de ander 20, daarnaast is het ene product gemakkelijker te vermarkten als het andere.

Mede hierdoor zie je de grote verschillen in getallen. Daarentegen zullen alle verenigingen aan het verlies bij moeten dragen dus moest ik een verdeling gaan maken. Er zijn op voorhand forecasts af gegeven wat een ieder in de planning had en dat zijn allemaal zaken welke al in de cijfers zoals aangeleerd verwerkt zaten.

[Fish]

Je grootste probleem is dat die formule van te voren bepaald had moeten worden en je leden er akkkoord mee hadden moeten gaan. Je kan nu zoveel bedenken en anders uitkomen. Nu wil de slechste vereniging natuurlijk meer middelen dan de beste,

Ga je ze op hand van de forcast en behaalde resultaten straffen. en ga je dat dan in absolute nummers doen
hoe heb je uberhaubt die percentages berekend

heel basaal absoluut
jij hebt er bijv 5 van de 15 contracten niet gehaald. en je verdeelt verhoudingsgewijs de boete over het aantal niet gehaalde.

of ga je het relatief doen
jij hebt 33% niet gehaald en vereniging 2 ook 33 % niet dus we verdelen de boete 50/50

Maar stel nu dat vereniging 2 100 van de 300 niet had gehaald dan is het percentage hetzelfde. maar verening 2 zal zich (terecht) veel te hard beboet voelen

Ga je een tussenvorm doen? mijn 1ste absolute oplossing vind ik zelf ronduit de eerlijkste (bij wat ik nu weet)

[Xorsist]

Ik ben welbewust vaag gebleven in het gehele verhaal omdat ik een achteraf simpele formule niet kon achterhalen. Alle vragen welke je steld zijn ondanks dat ze valide zijn in dit geval overbodig. Dit daar alle relevante zaken op voorhand al meegenomen zijn.

Nogmaals bedankt voor het meedenken, ik heb wat ik zocht, ik kon alleen op moment van zoeken mijn hoofd niet om het geheel heen krijgen (was al vanaf 5 uur bezig )

[Lustucru]

Xorsist schreef op donderdag 05 januari 2012 @ 20:25:
Okay om aan de intresse en vraagstellingen te voldoen bij deze een wat uitgebreider verhaal.
[...]knip

Maakt het -iets- begrijpelijker. Thx voor de update.