[Excel] Formule die met conditionele factor vermenigvuldigt

[Reveller]

Ik heb de volgende Excel (2007) worksheet:

	A	B	C	D
1	grens	factor	waarde	resultaat
2	20	1	17	17
3	50	2	34	68
4	150	3	45	90
5			60	180
6			200	600
7			400	1200

Eerst een korte uitleg, dan wat ik ermee wil:

• kolom A geeft bepaalde bovengrenzen aan
• kolom B geeft de factor waarmee de waardes uit kolom C vermenigvuldigt moeten worden, afhankelijk van de positie van die waarde ten opzichte van de grenswaarden. Dus: alle waarden uit kolom C t/m 20 moeten vermenigvuldigd worden met 1 (B2), alle waarden uit C die t/m 50 gaan (maar boven de 20 zitten!), moeten vermenigvuldigt worden met 2 (B3), etc.
• voorbeeld: de waarde C4 is groter dan 20 maar kleiner dan 51, dus het resultaat in D4 komt van 45 * 2, C7 is groter dan 150 (alles groter dan 150 vermenigvuldigen met 3), dus resultaat in D7 is 400 * 3 = 1200

Ik heb een conditionele formule nodig die de resultaten van kolom D uitrekent, zodat als ik de factoren in kolom B verander, of de bovengrenzen in kolom A, kolom D automatisch aagepast wordt. Ik heb alleen geen idee hoe hiermee te beginnen. Ik heb gezocht in Excel, en ben een functie tegengekomen waarmee cellen conditioneel kan kleuren, en er meer mee kan doen door zelf een statement te schrijven, maar daar gaat het dus verkeerd. Wie kan mij op weg helpen?

[mberns]

in kolom D een "als" formule gebruiken
Onderstaande zou moeten werken in cel D2 (volgens jouw gegeven tabel.)
Kopieer het vervolgens naar beneden.

=ALS(C2<=$A$2;$B$2*C2;ALS(EN(C2>$A$2;C2<=$A$3);$B$3*C2;ALS(EN(C2>$A$3;C2<=$A$4);$B$4*C2;ALS(C2>$A$4;C2*$B$4;""))))

De als formule is als volgt opgebouwd

=als(logische test ; waarde als waar ; waarde als niet waar)
wat de bovenstaande formule doet is

1 Controleren of C2 kleiner of gelijk is aan A2 (20)
2 In het geval van waar doet hij C2*B2
3 In het geval van niet waar, controleren of C2 groter is dan A2 (20) en kleiner dan of gelijk aan A3 (50)
4 In het geval van waar doet hij C2*B3
5 In het geval van niet waar, controleren of C2 groter is dan A3 (50) en kleiner dan of gelijk aan A4 (150)
6 In het geval van waar doet hij C2*B4
7 In het geval van niet waar, controleren of C2 groter is dan A4 (150)
8 In het geval van waar C2*B4
9 In het geval van niet waar “” (“” = niets)

Uit te breiden naar het aantal voorwaarden, worden dit er echter veel meer kijk dan eens naar een oplossing met vert.zoeken/vlookup

[ Voor 184% gewijzigd door mberns op 17-10-2008 16:12 ]

[Bolukan]

Probeer in kolom D eens =VERT.ZOEKEN($C2;$A$1:$B$3;2;Waar). Test even met 49,9; 50 en 50,1 of de uitkomsten zijn zoals je verwacht en pas de tabel in kolom B aan. Je moet ook nog een extra rijtje toevoegen (uit mijn geheugen moet je nog iets definieren voor >150, maar anders is het voor <20). Als het werkt zoals je verwacht *$C2 aan de formule toevoegen en klaar is Kees Reveller.

[Reveller]

mberns schreef op vrijdag 17 oktober 2008 @ 14:50:
in kolom D een "als" formule gebruiken
Onderstaande zou moeten werken in cel D2 (volgens jouw gegeven tabel.)
Kopieer het vervolgens naar beneden.

=ALS(C2<=$A$2;$B$2*C2;ALS(EN(C2>$A$2;C2<=$A$3);$B$3*C2;ALS(EN(C2>$A$3;C2<=$A$4);$B$4*C2;ALS(C2>$A$4;C2*$B$4;""))))

De als formule is als volgt opgebouwd

=als(logische test ; waarde als waar ; waarde als niet waar)
wat de bovenstaande formule doet is

1 Controleren of C2 kleiner of gelijk is aan A2 (20)
2 In het geval van waar doet hij C2*B2
3 In het geval van niet waar, controleren of C2 groter is dan A2 (20) en kleiner dan of gelijk aan A3 (50)
4 In het geval van waar doet hij C2*B3
5 In het geval van niet waar, controleren of C2 groter is dan A3 (50) en kleiner dan of gelijk aan A4 (150)
6 In het geval van waar doet hij C2*B4
7 In het geval van niet waar, controleren of C2 groter is dan A4 (150)
8 In het geval van waar C2*B4
9 In het geval van niet waar “” (“” = niets)

Ontzettend bedankt, dit werkt inderdaad. Alleen...in werkelijkheid is de tabel veel langer, en dan wordt deze formule denk ik te bewerkelijk

Uit te breiden naar het aantal voorwaarden, worden dit er echter veel meer kijk dan eens naar een oplossing met vert.zoeken/vlookup

Bolukan schreef op vrijdag 17 oktober 2008 @ 17:17:
Probeer in kolom D eens =VERT.ZOEKEN($C2;$A$1:$B$3;2;Waar). Test even met 49,9; 50 en 50,1 of de uitkomsten zijn zoals je verwacht en pas de tabel in kolom B aan. Je moet ook nog een extra rijtje toevoegen (uit mijn geheugen moet je nog iets definieren voor >150, maar anders is het voor <20). Als het werkt zoals je verwacht *$C2 aan de formule toevoegen en klaar is Kees Reveller.

Dat snap ik dus niet helemaal. Uitgaande van de tabel uit de startpost, moet ik ik voor elke cel een aparte VLOOKUP formule definieren?

[mberns]

nee niet voor iedere cel apart.
Je geeft aan dat hij moet zoeken naar de waarde in cel C2
Dit moet hij doen in het bereik A2 tot en met B7
Als resultaat geef de waarde weer in kolom 2 van het bereik (kolom B dus in dit geval)
De laatste variabele geeft aan of excel moet zoeken naar het exacte geval of mag benaderen.
In jouw geval waar, want het betreft een tussen liggende waarde.
vervolgens doe je de gevonden waarde nog even maal de waarde in C2.

ziet er dan zo uit:
=Vert.zoeken(C2;$A$2:$B$7;2;waar)*C2

Zoals je ziet staat het bereik vast dus je kun de formule nu gewoon naar beneden slepen D3 tot en met Dxx

Wat ik nog wel zou doen, is in kolom A duidelijk de grenswaarden opnemen
dus 0 / 20 / 20,1 / 50 / 50,1 / 150 (in geval van het voorbeeld) en daarachter in kolom B de bijbehorende factor (1 / 1 / 2 / 2 / 3 / 3)

en ja ik had geen zin om een tabel aan te maken.