Als waarde tussen twee waarde dan vermenigvuldigen met waarde

[Marakus]

Beste mensen,

Ik heb gezocht naar een oplossing voor mijn Excel “probleem” maar helaas niet kunnen vinden. Hopelijk kan ik op deze wijze verder geholpen worden

Als volgt:

Ik heb een kolom ‘Van’ en een kolom ‘Tot’ met hierin verschillende waarde. Bijvoorbeeld: ‘van 0 tot 25’ en ‘van 25 tot 50’ etc.

Bij deze bereiken hoort een kostenplaatje. Bijvoorbeeld: ‘van 0 tot 25’ = € 100,00 en ‘van 25 tot 50’ = € 200,00.

Wat Excel in dit geval moet doen is:

Als ik een waarde opgeef van bijvoorbeeld ‘20’ dan moet Excel dus 20 x € 100,00 = € 2.000,00 berekenen.

Dit alles heeft een redelijk groot bereik en waarden kunnen veranderen, m.a.w. volledige cel verwijzing is gewenst.

Ter illustratie heb ik een bestandje bijgevoegd (als ik een willekeurig aantal invoer in cal B:16 dan dient in cel C:16 de kosten automatisch berekend te worden):

Bekijk bijlage Voorbeeld Excel issue.xlsx

Alvast hartstikke bedankt voor de moeite! Zeer gewaardeerd

 

[spaarie]

Beste Markus

Plak deze cel in C17 en trek hem naar beneden...

=ALS(B16<25;B16*D3;(ALS(B16<50;B16*D4;(ALS(B16<75;B16*D5;(ALS(B16<100;B16*D6;(ALS(B16<125;B16*D7;(ALS(B16<150;B16*D8;(ALS(B16<200;B16*D9;(ALS(B16<300;B16*D10;(ALS(B16<400;B16*D11;(ALS(B16<500;B16*D12;)))))))))))))))))))

Succes!

 

[Thankyou]

Zo is de formule iets korter:

=ALS(OF(B16="";B16<=0);"";VERT.ZOEKEN(B16;B3:D12;3))*B16

Succes,

 

[Marakus]

Top! Hartstikke bedankt, beide werken inderdaad. De eerste die snap ik ook daadwerkelijk en de tweede wil nog niet helemaal zakken lol. Met dit soort dingen toch ook belangrijk dat je het snapt voor hergebruik in de toekomst maar daar kom ik nog wel uit

In ieder geval bedankt voor jullie reacties, lekker snel ook! :thumb:

 

[Thankyou]

=ALS(OF(B16="";B16<=0);"";[COLOR="#FF0000"]VERT.ZOEKEN(B16;B3:D12;3))*B16[/COLOR]

Het rode gedeelte doet wat jij vraagt (zie F1-Help). Het andere gedeelte is er alleen voor om een foutmelding te voorkomen als de cel leeg is of er een negatief getal ingevuld wordt. Succes,

 

[spaarie]

Ik moest er ook even over nadenken, maar dit kan natuurlijk ook...

Zou alleen de tabel in VERT.ZOEKEN formule absoluut maken om het door te kunnen trekken zoals hieronder:

=ALS(OF(B16="";B16<=0);"";VERT.ZOEKEN(B16;$B$3:$D$12;3))*B16

 

[Marakus]

Toch een aanvullende vraag Martin, wanneer je de benadering leeg laat in de functie VERT.ZOEKEN neemt ie dan gelijk "WAAR" aan als benadering?

Je geeft aan dat het eerste gedeelte er vooral voor is om foutmeldingen te voorkomen. Maar hoe bepaald Excel in dit geval in welke "range" (van/tot) het getal valt? Of doet verticaal zoeken dit zelf omdat de benadering WAAR (hoeft niet exact overeen te komen) is kijkt hij alleen naar de "van" waarde met een max van de opvolgende waarde (dus getal 44 doet hij goed omdat 44 onder de waarde 50 valt etc.).

Ben benieuwd

 

[DANIEL162]

Zet een ) op het het einde van de formule, dan blijft de cel leeg als in B16 geen getal in staat.
grtjs Daniel

 

[spaarie]

De formule vereenvoudigt:

Als B16 is leeg of B16 is kleiner dan 0; waarde als waar is leeg; waarde als onwaar VERT.ZOEKEN waarde B16 in tabel B3 tot D12 en deze vermenigvuldigen met B16...

Wat jij bedoelt zit in het vert.zoeken. Omdat Martin geen waarde heeft gegeven in de formule VERT.ZOEKEN 0/1 oftewel WAAR/ONWAAR zoekt hij de dichtsbijzijnde getal in de tabel op die in B16 staat....

VERT.ZOEKEN(B16;B3:D12;3;[COLOR="#FF0000"]0/1[/COLOR]))*B16

 

[Marakus]

mmmm bedankt voor de verduidelijking, toch nog een vraagje

Verticaal zoeken kijkt en vergelijkt met de eerste kolom oftewel kolom B en vermenigvuldigd met de prijs in kolom D. Zoals jij aangeeft zoekt hij het dichtstbijzijnde getal in de tabel met die in B16 staat.

Dan zou ik denken dat wanneer de waarde bijvoorbeeld 49 is dan ligt deze dichterbij rij 5 oftewel 50 (range van 50, tot 75), dus zou verwachten dat hij B16 maal 300 euro doet. Echter doet hij het goed en doet B16 maal 200 euro (range van 25, tot 50). M.a.w. mijn verwachting is dat hij toch op de één of andere manier ook rekening houd met kolom C (Tot)...

Hoop dat hij nog duidelijk is zo

 

[spaarie]

Hij is duidelijk, maar Martin had ook aangegeven > kijk even onder F1 help onder deze functie dan had je dit gelezen:

Als benaderen WAAR is of wordt weggelaten, wordt er een exact of een niet-exact overeenkomende waarde gevonden. Wanneer er geen exacte overeenkomst wordt gevonden, wordt de volgende hoogste waarde die kleiner is dan zoekwaarde als resultaat gegeven.
De waarden in de eerste kolom van tabelmatrix moeten in oplopende volgorde zijn gesorteerd, anders geeft VERT.ZOEKEN wellicht niet de juiste waarde als resultaat. Zie Gegevens sorteren voor meer informatie.

Als benaderen ONWAAR is, wordt er alleen naar een exacte overeenkomst gezocht. In dit geval hoeft u de waarden in de eerste kolom van tabelmatrix niet te sorteren. Wanneer er twee of meer waarden in de eerste kolom van tabelmatrix overeenkomen met de zoekwaarde, wordt de eerst gevonden waarde gebruikt. Wanneer er geen exacte overeenkomst wordt gevonden, resulteert de functie in de foutwaarde #N/B.

Heppie hem???

 

[Thankyou]

Sorry, was even ergens anders bezig.
Het klopt dat als je de waarde WAAR/ONWAAR (of 1/0) weg laat de formule automatisch uit gaat van WAAR (of 0). Bij ONWAAR zoekt de formule naar een exacte overeenkomst.
Iemand die veel met de formule VERT.ZOEKEN werkt zal niet steeds WAAR of ONWAAR willen tikken 1 of 0 is korter. 1=WAAR 0=ONWAAR

zoekt hij de dichtsbijzijnde getal in de tabel op die in B16 staat....

Toch klopt dat niet helemaal, als je 49 in tikt is 50 "dichterbij" en toch geeft hij het bedrag wat bij de 25 hoort . . .

Succes,

 

[DANIEL162]

Zorg dat de haken ) goed staan anders krijg je in C16 WAARDE te zien i.p.v een lege cel
Een hele kolom C met WAARDE vermeld is ook niet mooi voor het oog

 

[Marakus]

Hij is mij nu geheel duidelijk en excuus ik had inderdaad even onder help moeten kijken zoals aangegeven door Martin.

Heel vervelend maar nog één ander vraagje lol als ik in B16 0 invoer of leeg laat dan krijg ik #WAARDE! Daniel gaf eerder al aan een ) aan het einde te plaatsen maar dat pikt Excel niet. Hoe kan ik de formule aanpassen zodat een 0 of lege waarde wordt geaccepteerd?

Jullie zijn de beste!

EDIT: Zie nu het laatste bericht van Daniel, ga ik gelijk even proberen!

 

[DANIEL162]

Zorg dat de haken ) goed staan zoals hieronder
anders krijg je in C16 WAARDE te zien i.p.v een lege cel
Een hele kolom C met WAARDE vermeld is ook niet mooi voor het oog.

=ALS(OF(B16="";B16<=0);"";VERT.ZOEKEN(B16;B3:D12;3)*B16)

Eind goed alles goed

 

[Marakus]

Ja top het werkt Daniel! ik had net jouw bericht gemist toen ik het vroeg lol moest dus eerst de dubbele )) weg halen.

Hij gaat nu helemaal goed.

Iedereen bedankt! Top werk! Lekker snel en doeltreffend

 

[Thankyou]

@ Daniel162,
Je hebt gelijk, beter de laatste ) te verplaatsen.

Wel nog een tip voor jou: Gebruik de code tags (#) bij formules