Wiskundige formule voor in excel

Wiskunde is niet alleen een vak op school. Kom je ergens in de praktijk (bijvoorbeeld tijdens je werk) een wiskundig probleem tegen dan kun je hier om hulp vragen.
Plaats reactie
Joost10
Nieuw lid
Nieuw lid
Berichten: 3
Lid geworden op: 07 dec 2020, 10:58

Wiskundige formule voor in excel

Bericht door Joost10 » 07 dec 2020, 11:22

Hoi,

Ik heb een Excel-bestand waarin ik dingen bereken.
Ik heb nu een top 10 gemaakt van bepaalde bedrijven en daaraan wil ik een formule koppelen. Ik kom er echter niet uit.

Het doel van de formule is om de top 10 naar percentage te verdelen niet alleen gebaseerd op de positie maar ook gebaseerd op het scoreaantal dat het bedrijf heeft.

Positie Naam Score
1. Bedrijf A 11
2. Bedrijf B 12
3. Bedrijf C 15
4. Bedrijf D 15
5. Bedrijf E. 15
6. Bedrijf F 18
7. Bedrijf G 24
8. Bedrijf H 27
9. Bedrijf I 27
10 Bedrijf J 30

Ter verduidelijking: De score worden toegewezen op bepaalde criteria, die voor deze formule niet van belang zijn. Hoe lager het scoreaantal hoe hoger de positie. Positie 1 is het "beste" en 10 de "slechtste. De formule heeft als doel om de bedrijven in te delen naar aandeelpercentage; dus Bedrijf A hoort het grootste aandeel te hebben omdat die op positie 1 staat. Bedrijf B het tweede grootste aandeel etc. etc. Ik weet hoe ik een formule moet maken puur gebaseerd op de positie. Dit is namelijk door Bedrijf A 10 punten te geven en Bedrijf J 1 punt. Dan komt er een aflopende percentageverdeling uit van; 18,2%, 16,4%, 14,5%, 12,7%, 10,9%, 9,1%, 7,3%, 5,5%, 3,6% ,1,8%. Samen is dit 100%. Echter ik wil niet alleen rekening houden met de positie maar ook met de score. Want nu is Bedrijf A "iets" beter dan Bedrijf B, echter door veranderende omstandigheden kan Bedrijf A een score hebben van 3 ipv 11 en blijven de andere bedrijven onveranderd. In dat geval is Bedrijf A "veel" beter dan Bedrijf B en dit dient in uitdrukking te komen in het aandeelpercentage.

Ik hoop dat het niet te ingewikkeld is en dat jullie mij kunnen helpen met een formule.

Alvast bedankt.

Joost :D

arie
Moderator
Moderator
Berichten: 3911
Lid geworden op: 09 mei 2008, 09:19

Re: Wiskundige formule voor in excel

Bericht door arie » 09 dec 2020, 21:45

Als je alleen naar de ordening (= volgorde) kijkt, dan verlies je een groot deel van de informatie:
de werkelijke scores zeggen veel meer.
Als je die ook wil gebruiken, dan zou je dat kunnen doen door:
- eerst een (dalende) functie te kiezen die de score afbeeldt op een waardering,
- en dan die waarderingen te schalen naar waarderingen met een totaal van 100%.

Voorbeeld:

Afbeelding

In dit plaatje staan op de x-as de scores (maximale score in dit voorbeeld = 36), op de y-as de waardering van elke score.

In blauw een lineaire waardering (= waardering 1) voor een gegeven score:
\(\text{waardering(score)} = 1 - \frac{\text{score}}{36}\)
De waardering neemt hier bij toenemende scores geleidelijk af van 1 (bij score=0) tot 0 (bij score=36).

In rood een alternatieve waardering (= waardering 2) voor een gegeven score:
\(\text{waardering(score)} = 10 \cdot e^{-\text{score}/10}\)
Nu krijgen de lagere scores relatief een veel grotere (=hogere) waardering dan de hogere scores.

In Excel levert dit met jouw voorbeeld:

Afbeelding

Met enkele codes voor de cellen:
cel C2: =1-B2/36
cel D2: =100*C2/$C$12
cel E2: =10*EXP(-B2/10)
cel F2: =100*E2/$E$12

In waardering 1 krijgt bedrijf B met score 12 een aandeel van 14.46% en bedrijf G met een score van 24 (=2*12) een aandeel van 7.23% (=14.46/2).
Met de 2e functie, waardering 2, krijgen de goed presterende bedrijven (A t/m E) een groter aandeel, en de slecht presterende bedrijven (F t/m J) een lager aandeel dan bij waardering 1.

Je hebt alle vrijheid om zelf zo'n functie (van score naar waardering) te bedenken.


Hier nog de resultaten als bedrijf A geen score van 11 maar een score van 3 zou hebben (met dezelfde 2 waarderingsfuncties):

Afbeelding

Nu scoort bedrijf A met waardering 1 veel beter, en met waardering 2 heel veel beter.

Kom je hiermee verder?

Joost10
Nieuw lid
Nieuw lid
Berichten: 3
Lid geworden op: 07 dec 2020, 10:58

Re: Wiskundige formule voor in excel

Bericht door Joost10 » 10 dec 2020, 09:12

Heel uitgebreid antwoord. Ik ga hier even mee aan de slag. Ik kom er op terug.

Dank alvast voor de uitleg. :D

Joost10
Nieuw lid
Nieuw lid
Berichten: 3
Lid geworden op: 07 dec 2020, 10:58

Re: Wiskundige formule voor in excel

Bericht door Joost10 » 10 dec 2020, 12:27

Ja. Dit is wat ik zocht.

Het grootste probleem dat ik had was dat ik een lage score moest omzetten naar een hogere waardering en dat kon omzetten naar een relatieve verhouding.

Dat is nu gelukt en kan ik de waardering van de positie ook combineren met de relatieve verhouding van de score.

Heel erg bedankt. :D

Plaats reactie