Formule m.b.t. dubbele prijzen en verwijzing

[Johan09]

Goedemorgen,

In bijgaand bestand probeer ik aan de laagste prijs het correspondeerde land te zoeken en daarna bij de 2 en derde laagste prijzen ook het correspondeerde land te tonen. Nu kan het echter voorkomen dat de prijzen in landen gelijk zijn en met de formules welke ik tot nu toe heb ontwikkeld kan er geen onderscheid gemaakt worden bij welk land de prijs hoort in het geval van dubbele prijzen. Ik heb geen voorkeur welk land eerst getoond wordt bij gelijke prijzen.
Graag jullie advies/hulp om dit werkbaar te maken.

Ik hoor het graag,

gr Johan


* op vraag van de TS de bijlage aangepast *

 

[MarcelBeug]

Dat is met de gekozen layout nauwelijks te doen. In de bijlage een andere layout met de input in Q1:S5 en de resultaten in U1:W4.

Hierbij is een bekende truc toegepast om een fractie van het regelnummer op te tellen bij de prijzen, zodat de 5 op regel 2 eigenlijk 5,0000000002 wordt en de 5 op regel 3 wordt 5,0000000003 (kunnen meer of minder komma's zijn). Zodoende zijn er geen ex aequos meer. Voorwaarde is wel dat de prijzen een maximum aantal decimalen hebben (bijvoorbeeld 2) dat niet conflicteert met de fractionele bijtelling en dat de prijzen niet teveel cijfers vóór de komma hebben (i.c. max 6), anders vallen de bijgetelde fracties weg achter de komma.

Edit: de formules in V2 en W2 zijn gekopieerd naar beneden.

V2: =KLEINSTE(INDEX(($R$2:$R$5+RIJ($R$2:$R$5)/(10^10));0);U2)
W2: =INDEX($Q$2:$Q$5;VERGELIJKEN(V2;INDEX(($R$2:$R$5+RIJ($R$2:$R$5)/(10^10));0);0))

 

[Johan09]

Goedemiddag,

Bedankt voor de reactie. de format van het oorspronkelijke bestand (veel groter en veel meer data) staat helaas vast dus met deze opbouw moet ik het helaas doen.
Daarnaast staan er nogal wat getallen achter de komma's wat mij geen ruimte geeft om er waardes aan toe te voegen en daarnaast kan dat ook te veel verschil opleveren.

nauwelijks klinkt niet als onmogelijk ...?

gr

 

[Johan09]

Voor nu heb ik de formule van het land bij het zoeken naar de 2de prijs andere volgorde opgebouwd zodat als er een gelijke prijs is een ander land wordt getoond. .. dit lijkt ook te werken alleen netjes is anders

 

[MarcelBeug]

Nu dan de "nette" oplossing, hm, hm....

Edit: hij is nog niet helemaal 100% correct; ik zal hem nog verder testen en corrigeren. Met de huidige gegevens werkt hij wel.
Nadere edit: deze oplossing gaat fout zodra er "prijzen per stuk" zijn die hoger zijn dan prijzen. Zie post #6 voor een werkende oplossing.

Eerst heb ik een willekeurige cel op regel 3 geselecteerd, op tab Formules "Namen beheren" gekozen en de volgende 2 namen gedefinieerd:

[B]Prijzen:[/B] =ALS(REST(KOLOM(Blad1!$H3:$N3)-KOLOM(Blad1!$H3)+1;2);Blad1!$H3:$N3)
[B]Rangorde:[/B] =AANTAL.ALS(VERSCHUIVING(Blad1!$H3;;;;KOLOM(Blad1!$H3:$N3)-KOLOM(Blad1!$H3)+1);Prijzen)+AANTAL.ALS(Blad1!$H3:$N3;"<"&Prijzen)-3*REST(KOLOM(Blad1!$H3:$N3)-KOLOM(Blad1!$H3)+1;2)

Vervolgens in A3 de volgende formule, gekopieerd naar rechts tot en met F3:

=ALS(REST(KOLOMMEN($A3:A3);2);KLEINSTE(Prijzen;(KOLOMMEN($A3:A3)+1)/2);INDEX($G$1:$M$1;VERGELIJKEN(KOLOMMEN($A3:A3)/2;Rangorde;0)))

Mocht een en ander aangepast moeten worden op basis van de echte data, dan wijst het meeste zich vanzelf, maar wellicht minder duidelijk:
in de gedefinieerde naam Rangorde is de 3 in het stukje 3*REST gelijk aan het aantal tussenliggende kolommen "prijs per stuk" in het bereik startend van de eerste prijs (nu H3) tot en met de laatste prijs (nu N3), nu dus 3: I, K en M.

En niet vergeten: de formules van de gedefinieerde namen moeten overeenstemmen met de regel waarvandaan "Namen Beheren" is gekozen.

Ter illustratie: als ik vanaf regel 4 naar "Namen beheren" ga, dan is de formule voor Prijzen:

=ALS(REST(KOLOM(Blad1!$H4:$N4)-KOLOM(Blad1!$H4)+1;2);Blad1!$H4:$N4)

dus dat schuift mee op over de regels heen.

Ik leg het maar even uit, want morgen heb ik zelf ook geen flauw idee meer wat ik allemaal gedaan heb. :d
Maar het werkt wel! Zoals je al opmerkte "nauwelijks klinkt niet als onmogelijk".

 

[MarcelBeug]

Bij nader inzien lukt het me niet om de oplossing uit post #5 waterdicht te maken.
Het probleem zit hem in het feit dat de prijzen niet in een aaneengesloten bereik staan.

Plan C (A en B hadden we in #2 en #5) is nu om met hulpvelden te werken met aaneengesloten prijzen:

In P3, gekopieerd naar rechts tot en met S3:

=INDEX($G3:$N3;2*KOLOMMEN($P3:P3))

Ik heb nog maar 1 gedefinieerde naam:

Rangorde: =AANTAL.ALS(VERSCHUIVING(Blad1!$P3;;;;KOLOM(Blad1!$P3:$S3)-KOLOM(Blad1!$P3)+1);Blad1!$P3:$S3)+AANTAL.ALS(Blad1!$P3:$S3;"<"&Blad1!$P3:$S3)

Vervolgens in A3 de volgende formule, gekopieerd naar rechts tot en met F3:

=ALS(REST(KOLOMMEN($A3:A3);2);KLEINSTE($P3:$S3;(KOLOMMEN($A3:A3)+1)/2);INDEX($F$1:$M$1;2*VERGELIJKEN(KOLOMMEN($A3:A3)/2;Rangorde;0)))

 

[Johan09]

Thanks erg knap