Office: SVerweis: nächsthöhere Zahl finden!

Hallo zusammen,

ich komme bei folgendem problem nicht weiter:

hier stark vereinfachte darstellung mit wenigen zahlen:

ich habe eine matrix mit spalte A und B:

spalte A überschrift: " Umsatz bis TEUR"

spalte B überrschrift: "Höhe der Provision"

spalte A hat ab zeile 1 z.b. folgende zahlen: 100,150,200,250

spalte B hat ab zeile 1 z.b. folgende zahlen: 20,24,30,41

Nun gibt es noch eine Eingabezelle, C1 hier will ich beliebige zahlen zwischen 100 und 250 eingeben.

jetzt kommen wir zur formelzelle, z.b. D1:

Der sinn der übung: ich will errechnen lassen, wieviel provision jeweils bei eingabe einer beliebigen zahl zwischen 100 und 250 fällig wird.

Beispiel: Eingabe in C1 = 100, Ergebnis in Formelzelle D1 = 20 (also die zahl in spalte b neben "100" in spalte a)

JETZT DAS PROBLEM:
Beispiel: Eingabe in C1 = 155 Ergebnis in Formelzelle D1 = 24! ES MÜSSTE ABER 30 sein( da umsatz zwischen 151 und 200 = 30 provision ist)

Meine Formel mit Sverweis (oder auch mit summewenn) funktioniert nur, wenn ich genau die zahl eingebe, die in der spalte A enthalten ist!

Gebe ich hingegen z.b. 155 ein, so wirft er mir (fälschlicherweise) die "24" aus, wenn letztes argument "wahr" bzw. nix drin steht"; andernfalls kommt # NV, da er ja keine GENaUE entsprechung gefunden hat.

FRAGE (sorry, wenn ich es so kompliziert erklärt habe):

Wie kann ich die formel anpassen, daß für den fall, dass keine entsprechung in spalte A gefunden wird (155) dass er dann ganz klug sich die NÄCHSTHÖHERE zahl in der spalte A sucht, und dann noch den danebenstehenden wert in spalte B auswirft???

Problem verstanden?

Zur Sicherheit das ganze noch mal als excel-sheet....

Ist das irgendwie lösbar?

gruss amavisca

Vielen DAnk für eine Rückmeldung

 

Hallo!

	A	B	C	D
1	100	20	155	30
2	150	24
3	200	30
4	250	41
5

Zelle	Formel
D1	{=INDEX(B:B;MIN(WENN(A1:A999>=C1;ZEILE(1:999))))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Aber warum konstruierst du deine Referenz-Liste mit "bis"-Werten? Gestalte sie mit "ab"-Werten und dein SVERWEIS funktioniert mit WAHR wunderbar...

 

Hallo,

ohne Matrixformel:

Arbeitsblatt mit dem Namen 'stbgebV'
	A	B	C	D	E	F	G
6	Werteingabe	155				Zugrundeliegenden Daten
7						bis TEUR..	Honorar =
8						100	20
9	Honorar	40				150	30
10						200	40
11						250	50

Zelle	Formel
B9	=INDEX(G8:G11;VERGLEICH(B6;$F$8:$F$11;1)+ISTNV(VERGLEICH(B6;$F$8:$F$11;0)))

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Gruß

Edgar

 

Hallo!

Mist, ich hab' den Kürzeren... :-D :-D :-D

=INDEX(B:B;VERGLEICH(C1;A:A)+(ZÄHLENWENN(A:A;C1)=0))

(als viagra-freie ääähhhh matrix-freie Version.)

 

Hallo Schatzi,

es wird immer kürzer. Ist nicht immer von Vorteil.
Aber so wäre es noch kürzer:

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B	C	D
1	Werteingabe	155	Zugrundeliegenden Daten
2			ab TEUR..	Honorar =
3			0	20
4	Honorar	40	100	30
5			150	40
6			200	50

Zelle	Formel
B4	=INDEX(D:D;VERGLEICH(B1;C:C;1))

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]

Gruß

Edgar

 

Hallo!

Wem sagst du das...? :cry:

Aber als "ehrenamtlicher Klugscheisser" muss ich damit leben...

PS: Es kommt ja (angeblich) nicht immer auf die Länge an!!!!!!!!!!!!!!!

 

ähem.......

also das problem scheint ja so banal gewesen zu sein, daß ihr euch noch in varianten überbietet!....:-)

hier werden sie geholfen, vielen, vielen dank!!!

An der index-funktion habe ich irgendwie auch rumgegraben, aber ehrlicherweise blicke ich die nicht trotz mehrfachem lesen in der excel-hilfe.....(ich habe halt andere stärken...:-)

aber mit eurer hilfe schaffe ich es jetzt vielleicht mal, an einem konkreten beispiel hinter das geheimnis (jedenfalls für mich) von INDEX und VERGLEICH zu kommen!!

Gruss und bis zur nächsten banalen frage
AMAVISCA

 

Hallo,

VERGLEICH sucht einen Wert (hier den Wert, der in B1 steht) in einer Matrix (hier C:C) und gibt die Zeilennummer zurück, in der
a) bei 0 der genaue Wert steht,
b) bei 1 in einer aufsteigend sortierten Liste der nächstkleinere oder gleiche Wert steht
c) bei -1 in einer absteigend sortierten Liste der nächstgrößere oder gleiche Wert steht

INDEX zeigt den Wert in einer anderen Spalte (hier D:D) an, der in der Zeile steht, die Vergleich ermittelt hat.

Deswegen habe ich auch die 0 in Spalte C eingefügt, damit die Werte verschoben sind. Könnte aber Probleme geben, wenn genau 150 gesucht wird, dann zeigt die Formel einen falschen Wert. Besser wäre es, 150,001 einzutragen, entsprechend auch bei den anderen Zahlen.


Gruß

Edgar

 

Hallo Kallerwirsch,

ich wollte es nicht versäumen, mich dafür zu bedanken, daß Du mir die 2 Funktionen in so einem Deutsch erklärt hast, daß ich es auf anhieb verstanden habe!

Ich baue seit tagen nur noch excel-formeln mit index und vergleich...

gruss amavisca

P.S. Hab schon ein nächstes problem im köcher, muss mich aber erst mal umschauen, ob es dazu schon ein thema gibt....