[Excel] Percentiles met voorwaarden uitrekenen mbv INDEX

[Verwijderd]

Ben op het moment bezig met een excelsheet en probeer bepaalde percentiles uit te rekenen. Maar dit lukt niet echt, dus ik hoop dat jullie mij advies kunnen geven.
Ik heb 2 onafhankelijke variabelen (MV en BEME) en afhankelijke variabelen (Size en HML).
Nu maak ik de Size variabele door de mediaan te pakken en alles kleiner dan de mediaan is 1S en alles groter 2B.
IF(A1<MEDIAN($A$1:$A$9900);"1S";"2B"))

De HML wordt gemaakt door de BEME variabele in 3 stukken breken: onderste 30% is 1H, middelste 40% is 2M en hoogste 30% is 3H.
IF(B1<=PERCENTILE($B$3:$B$9900;0.3);"1L";IF(B1<PERCENTILE($B$3:$B$9900;0.7);"2M";"3H")

Dit is allemaal nog te doen.

Maar nu moet de HML variabele gemaakt worden op voorwaarde van de waarde van Size. Dus alle Size 1S moet ik drie stukken worden gebroken en 2B moet in drie stukken worden gebroken.
Dus als Excel naar de percentiles moet kijken, dan moet dat alleen naar de percentiles van 1S of 2B.

Heb wat lopen pielen met de Index/match functie maar kom ik niet helmaal mee uit. Heb bijvoorbeeld dit geprobeerd voor de 1S size variabele, maar dan pakt hij op een of andere reden de 2M waarde niet:

IF(C15="1S";IF(B15<=PERCENTILE(INDEX($B$3:$B$9900;MATCH("1S";$C$3:$C$9900;0));0.3);"1L";IF(B15<=PERCENTILE(INDEX($B$3:$B$2036;MATCH("1S";$C$3:$C$2036;0));0.7);"2M";"3H")))

Hebben jullie een (misschien wel makkelijkere) manier op dit op te lossen? Alvast heel erg bedankt.

Mijn data ziet er kort door de bocht zo uit (zijn rijen van 5000+):

MV     BEME    Size   HML
100     0.5         1S      x
500     1.0         2B      x
700     0.7         2B      x
50       1.1         1S      x
600     1.5         2B      x
120     0.9         1S      x
550     0.1          2B     x
150     0.2          1S     x

[Reptile209]

Kan je je data niet eerst gewoon sorteren op Size? Of een paar hulpkolommen/sheets te gebruiken die dat doen? Dan is het daarna een kwestie van binnen de twee blokken de HML bepalen. Nu zit je jezelf enorm in bochten te wringen om de juiste combinaties te maken...
Als dat niet kan, zou ik persoonlijk voor een VBA-oplossing gaan (lees: ik kom er ook niet uit om dit in één formule te gooien).

Voor degenen die je verhaal ook niet in één keer konden bevatten:
Stap 1: bepaal 'Size' (1S of 1B) op basis van de mediaan van kolom 'MV'
Stap 2: bepaal 'HML' op basis van een percentiel van kolom 'BEME', waarbij het percentiel apart voor de groep 1S en 1B wordt bepaald. (ergo: sorteer op Size en bepaal dan het percentiel in BEME ).

[ Voor 30% gewijzigd door Reptile209 op 17-06-2010 16:08 ]

[pedorus]

Ik denk aan

code:

{=LOOKUP(PERCENTRANK(IF(C$2:C$9=C2,B$2:B$9),B2),{0,0.3,0.7},{"1l","2m","3h"})}

Dit is een array-formule, in te voeren met ctrl-shift-enter om die haken eromheen te krijgen. Verder heb je gekke instellingen (scheidingsteken ; en decimaalteken . komt niet vaak voor ), dus dan wordt het:

code:

{=LOOKUP(PERCENTRANK(IF(C$2:C$9=C2;B$2:B$9);B2);{0;0.3;0.7};{"1l";"2m";"3h"})}

Uitgegaan van je voorbeelddata.

[Verwijderd]

Allebei erg bedankt voor jullie reactie! Reptile, jouw uitleg is idd wat duidelijker dan die van mij, zit iets teveel in de excelsheets nu... Heb jij een link naar een website waar een tutorial van VBA? Want met formules kan ik redelijk omgaan, maar VBA helaas (nog) niet.

Pedorus, ook heel erg bedankt, maar jouw methode geeft hetzelfde probleem dat ik nu heb, namelijk dat hij nu niet evenredige breekpunten geeft. Dus hij pakt niet de percentielen op basis van alleen 1S of 2B, maar de percentielen van het totaal (of ik doe iets verkeerd).
Heb een excelbestandje snel gemaakt met je formule om te illustreren wat er gebeurd.
Alle combinaties moeten gelijkverdeeld zijn (in het voorbeeld met N=20:
1S1L = 3
1S2M = 4
1S3H = 3
2B1L = 3
2B2M = 4
1S3H = 3

Link naar excel bestandje

Nogmaals erg bedankt jullie feedback!

[pedorus]

Ik zie een aantal dingen:

• Je hebt c$9 laten staan, maar dit moet c$23 zijn omdat je voorbeeld groter is.
• Je hebt geen ctrl-shift-enter gebruikt, maar gewoon enter, waardoor je de {} mist en je een verkeerd resultaat krijgt.
• Je hebt het aantal 1S handmatig verkeerd geteld.
• Bij 2B krijg je een dubbele bij 1L, maar dat maakt nu toevallig sowieso niet uit omdat exact 0.3 ook 1L moet geven
• Bij 2B heb je exact 0.7, dit geeft nu 3H maar moet 2M zijn zie ik nu als je de originele formules wil aanhouden.

Een oplossing voor dit laatste is vanuit het negatieve te werken, dan krijg je dat de scheiding andersom is, dus:

code:

{=LOOKUP(-PERCENTRANK(IF(D$3:D$23=D3,C$3:C$23),C3),{-1;-0.7;-0.3},{"3H";"2M";"1L"})}

[Verwijderd]

Nogmaals erg bedankt, hier kom ik idd mee verder.
- Over cntrl-shift-enter, was idd vergeten om in de formulebalk cntrl-shift-enter te typen...
Voor mijn info, normaal gesproken kan ik dit doen voor een hele rij, maar klopt het dat ik het nu één voor één moet doen? Want als ik de hele rij selecteer dan krijg ik of 3H of 1L (afhankelijk of ik de positieve of negatieve versie van jouw formule gebruik).
Aangezien ik dadelijk wil proberen in een rij van een hele hoop observaties (5000+), gaat het handmatig cntrl-shift enter geen pretje worden. Doe ik iets verkeerd?

- Had één 1S teveel neergezet, maar de formule (waar 2B in stond klopte wel).

De dubbele/verkeerde observaties komen omdat de sommige getallen in dit voorbeeld even groot zijn. Aangezien ik met data werk waar geen enkel getal hetzelfde is (hebben allemaal een heleboel cijfers achter de komma), moet dit goedkomen.

Maar dit werkt volgens mij heel goed! Had er al twee dagen mee lopen stoeien, dus ben erg blij dat je me kan helpen.

[pedorus]

Verwijderd schreef op vrijdag 18 juni 2010 @ 13:25:
Aangezien ik dadelijk wil proberen in een rij van een hele hoop observaties (5000+), gaat het handmatig cntrl-shift enter geen pretje worden. Doe ik iets verkeerd?

Gewoon het alleen in de eerste cel zetten, en dan doortrekken op de normale manier (zeg dubbelklikken op kruisje rechtssonder cel). Je kan inderdaad niet eerst de boel selecteren als een array en dan in 1x de formule toewijzen voor die array, deze formule geeft maar 1 resultaat.

[Verwijderd]

Top! Nogmaals bedankt, ik kan nu verder.