Help met formule

[Mulderalexander]

Hi All,

Ik vraag me af of iemand me kan helpen met de onderstaande formule:

=SUMPRODUCT(("OSAT " &B2=ratings)*(brands=$A2)*ratings_values)/COUNTIF(brands;$A2)

In de bijgevoegde sheet zie je in SHEET2 dat cel B3 met deze formule is gedaan, maar aangezien ik de formule niet helemaal snap kan ik 'em niet aanpassen naar wat ik wil...

Ik wil namelijk per BRAND zie SHEET1 (FPbS, WST, SH, LC en STR) een tabel maken (in SHEET2) met daarin in een kolom onder elkaar met de gemiddelde score van LOY (Loyalty Comp) PHYS (Physical Comp), ARR (arrival comp), SERV (service comp) en F&B (F&B comp) die in SHEET1 gegeven worden (voobeeld in SHEET2). Je kan in SHEET1 namelijk zien dat er per brand verschillende kantoren zijn en in cellen H1 t/m AQ1 staan scores (GOALS niet meetellen). Ik wil dat de formule de scores neemt die bij de kantoren van een BRAND horen en per Comp (Composite) deze in de tabel zet. Ook is er een verschil tussen MTD en MTD LY die ook in de tabel staan en waar dus ook een verschil tussen moet zijn.

De formule die ik nu heb zijn de ranges:
'ratings' = uit je brongegevens (sheet1) de range: H1:AR1 --> kolomtitels alle waarden
'brands' = uit je brongegevens (sheet1) de range: D2: D34 --> alle brands
'ratings_values' = uit je brongegevens (sheet1) de range: H2:AR34 --> alle waarden

(hoop dat dit uberhaupt klopt maar dat denk ik wel)

Ik hoop dat het duidelijk is en dat iemand me kan helpen....

Alvast ontzettend bedankt want dit bezorgt me al de hele dag kopzorgen.

 

[wiki]

Er is geen bijlage toegevoegd!

mvg Wim

 

[Mulderalexander]

Er is geen bijlage toegevoegd!

mvg Wim

Hij was iets te groot, heb het aangepast. Dank je

 

[danny147]

Beste Mulderalexander

Zie bestandje.
Bij LM geeft hij als uitkomst #DEEL/0!
Dit komt omdat LM niet voorkomt op tabblad1.

Dit kan je wegwerken door overal voor de formule =ALS(ISFOUT(formule;"";formule) te zetten.

Voor cel L20 wordt dat:

=ALS(ISFOUT(SOMPRODUCT((K19&" "&$L$18=ratings)*(brands=$K$18)*ratings_values)/AANTAL.ALS(brands;$K$18));"";SOMPRODUCT((K19&" "&$L$18=ratings)*(brands=$K$18)*ratings_values)/AANTAL.ALS(brands;$K$18))

Groetjes Danny. :thumb:

 

[Mulderalexander]

Beste Danny,

Ontzettend bedankt voor je hulp!

Heeft echt onwijs geholpen. Het enige kleine probleem waar ik nu mee zit is dat ik de formule moet overnemen voor het echte bestand. De info die ik hier had gepost was iets anders ivm vertrouwelijke informatie.

Kan je me uitleggen hoe ik de waardes van "rating", "brand" en "ratings_values" bepaal? Ik weet wel dat ze uit sheet 1 komen en daar alle info beslaan waar het uit gehaald moet worden maar hoe doe ik dit dus zelf?

Dank je!

 

[danny147]

Beste Mulderalexander

Kijk in het naamvak boven cel A1.
Daar zie je ze alle drie staan, "rating", "brand" en "ratings_values"
Klik op 1 ervan en zie wat voor bereik ze hebben.
Om dit zelf te doen ga dan als volgt te werk.

Selecteer een bereik en geef het een naam in het naamvak en afsluiten met ENTER.
Nu moet je het bereik in de cel niet meer ingeven enkel de naam die je er aan gegeven hebt.

Groetjes Danny. :thumb:

 

[Mulderalexander]

Okay top die heb ik nu door!

Ik ben nog een beetje aan het worstelen omdat als ik dus de formule aanpas voor mijn versie van de data ik een andere score krijg die ik zou meoten hebben

Ik heb namelijk ook diverse regio's die ik in acht moet nemen, dus hoe voeg ik extra aan de formule toe dat ie bijvoorbeeld niet alleen naar de correcte brand kijkt maar ook naar wat ik heb, namelijk regio's?

En snap ik het begin van de formule goed als ik zeg dat:

=SUMPRODUCT((F4&" "&$G&2=ratings) betekend dat ie nu naar de loyalty composite kijkt EN naar MTD? Dus hij neemt in de kolom MTD niet de scores in acht van MTD LY die in de originele data staan?

Ik ga nog even verder worstelen, sowieso met de score die ik eruit krijg maar kan ik je eventueel morgen lastig vallen als het me niet mocht lukken? Weet dat dit veel gevraagd is maar zou het echt super vinden om dit te snappen en het voor elkaar te krijgen!

Bedankt,

Alex

 

[danny147]

Beste Mulderalexander

De volgende formule betekent:

=SOMPRODUCT((F4&" "&$G$2=ratings)*(brands=$F$2)*ratings_values)/AANTAL.ALS(brands;$F$2)

F4 = Loyalty Comp
&" " = spatie
&G2 = MTD

In totaal is dit "Loyalty Comp MTD" dit wordt gezocht in het bereik ratings (H1:AR1 op tabblad2) Dit staat in cel T1
Indien dit WAAR is wordt er gezocht naar de 2de matrix.

F2 = FPbS
Dit wordt gezocht in het bereik brands (D2: D34 op tabblad2) Dit staat in cel D2
Indien dit WAAR is wordt er gezocht naar de 3de matrix.

De 3de matrix vraagt om het gemiddelde te nemen van de cel of cellen die overeenkomen met matrix1 en matrix2 in het bereik ratings_values (H2:AR34)
In dit geval is er maar één cel nl. T2 en dit geeft als resultaat 8,67

Groetjes Danny. :thumb:

 

[Mulderalexander]

Danny,

ontzettend bedankt voor de uitleg.

Ben er dus alleen net achter gekomen dat ze het anders uitgerekend willen hebben dan ik dacht....

Wat ze nu doen is de score per kantoor nemen. Dus bijvoorbeeld SH Kantoor A heeft voor Loyalty MTD een score van 8.01 en die score is bereikt door de antwoorden van 15 klanten. Kantoor B heeft een score van 7.04 en dat is bereikt door de antwoorden van 53 klanten. Wat ze gedaan hebben om de score aan de hand van een pivot table te berekenen is (8.01 * 15) + (7.04 * 53) = 493.27 en dit hebben ze gedeeld door het totaal aantal respondenten om de score MTD voor LOY te krijgen voor BRAND SH. Dus eens core van 7.25. (voorbeeld)

Is het mogelijk om deze optie zo toe te voegen aan de formule? Dat ik niet het gemiddelde neem van de totale score maar een wat gedetaileerde score doormiddel van het gebruik maken van weighted averages...

 

[danny147]

Beste Mulderalexander

Waar kan ik het aantal klanten vinden ?

Groetjes Danny. :thumb:

 

[Mulderalexander]

Beste Danny,

Staat onder Responses en dan heb je weer Repsonses MTD (kolom J) en MTD LY (kolom K)

Thanks! :thumb::thumb:

 

[danny147]

Beste Mulderalexander

Zo dan, zie bestandje.

Groetjes Danny. :thumb:

 

[Mulderalexander]

beste danny,

wederom bedankt voor je hulp!

Kan je een beetje uitleggen wat je verandert hebt? Ik zie namelijk in SHEET1 dat je nu ook kolommen hebt gemaakt voor RESPONSES_MTD en RESPONSES_MTD_LY maar zie ze niet terug in de formule.

Als ik het goed heb uitgelegd zou de formule nu dus per kantoor de score moeten vermenigvuldigen met het aantal responses maar volgens mij doet ie dat niet.

Bijvoorbeeld:

van BRAND WT een Loyalty Comp van een bepaald kantoor, kantoor A4.

Van deze is de score 8.64 en er hebben 21 mensen geantwoord. Dus dan zou de formule 8.64 moeten vermenigvuldigen met 21. Dit zou ie voor alle kantoren voor Loyalty Comp voor BRAND WT moeten doen, deze scores bij elkaar optellen en dan delen door het totaal aantal respondenten om de score te krijgen.

In het geval van BRAND WT is dat dus:

Loyalty Comp MTD scores * Aantal responses
8.55 * 116 = 991.80
9.38 * 12 = 112.56
7.91 * 59 = 466.69
8.02 * 44 = 352.88
7.75 * 102 = 790.50
8.58 * 189 = 1621.62
8.32 * 125 = 1040.00

Totaal is dan 5375.25 en dat weer delen door alle respondenten (647) en dan kom je op een gemiddelde score voor BRAND WT voor Loyalty Comp MTD van 8.31.

En de formule moet dat dus doen per Brand en per Comp (Loyalty, Physical, Arrival, service, en F&B) voor MTD en MTD_LY

Het is zo onwijs lastig dat ik gewoon geen idee heb hoe je dat zou moeten doen...
Het is dus in feite hetzelfde wat je doet met een PIVOT table maar ik kan niet linken naar een pivot table omdat de cellen telkens veranderen als je een andere waarde selecteerd (is het te statisch voor heb ik al erg uitgebreid geprobeerd maar dat kan dus echt niet...)

Hoop dat je me uit de brand kan helpen en zou je echt willen bedanken voor al je hulp. Jammer dat ik niet weet wie je bent want anders zou je sowieso een goed flesje wijn van me krijgen! Maar ik denk dat dat wel te regelen valt of niet

 

[danny147]

Beste Mulderalexander

Kan je een beetje uitleggen wat je verandert hebt? Ik zie namelijk in SHEET1 dat je nu ook kolommen hebt gemaakt voor RESPONSES_MTD en RESPONSES_MTD_LY maar zie ze niet terug in de formule.

Deze formules kan je terug vinden in de kolommen VAR.

van BRAND WT een Loyalty Comp van een bepaald kantoor, kantoor A4.

BRAND WT vind ik nergens terug, alleen WST en kantoor A4 hoort bij STR

Groetjes Danny. :thumb:

 

[Mulderalexander]

Beste Danny,

Sorry ik bedoelde inderdaad WST en inderdaad niet Kantoor A4 maar kantoor A5. Even fout gelezen, maar het idee blijft hetzelfde.

Snap je wat ik bedoel? Met de soort formule die ik zoek?

 

[danny147]

Beste Mulderalexander

Heb eens gekeken naar hetgene dat je wou realiseren.

Loyalty Comp MTD scores * Aantal responses

9.38 * 12 = 112.56
7.91 * 59 = 466.69
8.02 * 44 = 352.88
7.75 * 102 = 790.50
8.58 * 189 = 1621.62
8.32 * 125 = 1040.00

Totaal is dan 4384,25 en dat weer delen door alle respondenten (531) en dan kom je op een gemiddelde score voor BRAND WST voor Loyalty Comp MTD van 8,25

Om deze gegevens eruit te halen is niet simpel hoor.
Met de formules VERT.ZOEKEN en SOMPRODUCT lukt dit al niet.

Je moet eerst alle gegevens van WST gaan rangschikken in een aparte tabel en dit kan met de formule MULTIVERT.ZOEKEN (speciale formule)

Dan kan men de aparte regels een voor een gaan vermenigvuldigen, optellen en het gemiddelde bepalen.

Een hele klus, ik zal zien wat ik kan doen van het weekend want dit zal veel tijd kosten.

Met één fles wijn zal je niet toekomen denk ik

Groetjes Danny. :thumb:

PS: kan je ook je e-mail adres eens doorsturen want het bestand wordt te groot om dit hier nog te kunnen plaatsen.

 

[Mulderalexander]

Beste Danny,

Ik was al inderdaad bang dat het erg lastig zou worden

Mocht je er tijd aan willen besteden zou ik dat natuurlijk geweldig vinden en dan zorg ik wel dat er een paar flesjes bij je terecht komen

Mn email adres is mulderalexander@mac.com

groetjes Alex

 

[danny147]

Beste Mulderalexander

Kan je eens kijken of deze cijfers kloppen vooralleer ik verder ga, want anders is al het werk voor niks geweest.

Heb enkel de waarden geplakt in een bestandje.
Bij MTD heb ik alle kantoren vermenigvuldigd met een item en gedeeld door de responses
Hetzelfde voor MTD LY en bij VAR het gemiddelde genomen van de 2.

Groetjes Danny. :thumb: