Wert vergleichen mit anderen Werten?
Ich möchte dass in der gelben, leeren Zelle der richtige Wert interpoliert steht.
Also die gelbe Zelle soll überprüfen ob 1, 2 oder 3 und dann, in dem Fall, die Werte von 20 und 30 auf 23 interpolieren. Das Ergebnis müsste hier also 15833,1 sein.
ABER: In der echten Tabelle sind die Werte von links nach rechts und von oben nach unten aufsteigend. B2 ist also der kleinste Wert und D7 der größte.
Kann mir da jemand ne Formel basteln?
2 Antworten
Erstmal die Werte in der Tabelle finden:
Den Wert in 2/20 kriegen wir mit:
=INDEX(B2:D7;GANZZAHL(A11/10)+1;B11)
und den darunter mit:
=INDEX(B2:D7;GANZZAHL(A11/10)+2;B11)
ich nenne die beiden der Einfachheit halber mal "lower" und "upper".
Dann brauchen wir die lineare Position zwischen den beiden Werten. Dazu einfach die letzte Ziffer der 23 durch 10 teilen und wir kommen auf 30%.
linearPos:
=RECHTS(A11;1)/10
Und jetzt die eigentliche Interpolation: Wir brauchen die Differenz zwischen den beiden Werten, berechnen daraus den Wert an der linearen Position und müssen natürlich den "lower" Wert wieder draufrechnen (das hast du vergessen), damit wir auf das Ergebnis kommen.
=(upper-lower)*linearPos+lower
Und damit man nicht so viele benannte Bereiche hat, kann man das ganze ganz nett in einer LET-Funktion zusammenfügen:
=LET(lower; INDEX(B2:D7;GANZZAHL(A11/10)+1;B11);
upper; INDEX(B2:D7;GANZZAHL(A11/10)+2;B11);
linearPos; RECHTS(A11;1)/10;
(upper-lower)*linearPos+lower
)
In meinem Excel gibt es =LET nicht. Was mache ich falsch?
Dann hast du wahrscheinlich eine ältere Excel-Version. In dem Fall musst du die Formel doch als einen Block bauen. Das ist unübersichtlicher, aber macht genau dasselbe.
=(INDEX(B2:D8;GANZZAHL(A11/10)+2;B11)-INDEX(B2:D8;GANZZAHL(A11/10)+1;B11))*REST(A11;10)/10+INDEX(B2:D8;GANZZAHL(A11/10)+1;B11)
Oder alternativ kannst du die Zwischenergebnisse jeweils in eine Zelle schreiben und den Zellen einen Namen geben (links neben dem Formel-Feld ist ein Feld, wo die Adresse der Zelle drinsteht, da kannst du einfach einen Namen reinschreiben). Dann kannst du die finale Berechnung z.B. so machen
=(upper-lower)*linearPos+lower
Bist du dir sicher, dass 15833,1 richtig ist? Das ist der Wert von 0 an gerechnet. Normalerweise müsstest du noch 3569 addieren.
Wie auch immer. Du suchst eine Funktion, die bestimmt, zwischen welchen zwei Werten dein Wert liegt.
Du könntest die Grenzen dir ersteinmal so berechnen:
Untere Grenze:
=GANZZAHL(A11/10)*10
Obere Grenze:
=GANZZAHL(A11/10+1)*10
Den Wert hinter der Grenze passend zur Spalte mit dem Wert 2 bekommst du per SVERWEIS.
=SVERWEIS(<untere Grenze>;A1:D7;B11+1);
Dann hast du die beiden Werte hinter den Grenzen, bildest die Summe und teilst durch die 10 Einzelschritte. Multipliziert mit A11-<untere Grenze> erhältst du den Offset für den Wert bei der unteren Grenze.
Vielen Dank, ich befürchte nur ich schaffe es nicht das auf mein Problem anzuwenden. Mein Kopf raucht schon.
Ich mach jetzt erst Mal eine Pause und versuche es dann mit deinen Tips nochmal.
Ich bekomme mit SVERWEIS nur den unteren Wert (im Bsp.: 3569). Wie bekomme ich den oberen Wert (im Bsp.: 56346)?
Du musst den Wert 23 auf den nächsten 10er aufrunden, bevor du auf die Suche gehst.
Zum Beispiel so:
=AUFRUNDEN(23;-1)
Meine Formel produziert noch zwei Probleme:
Wenn man einfach die letzte Ziffer von A11 nimmt, kann man keine Kommazahlen benutzen und wenn man 50 reinschreibt, kriegt man einen #BEZUG Fehler.
Um den Bezug-Fehler zu verhindern muss man den Bereich für die Index-Funktion eine Zeile größer machen und um Kommazahlen benutzen zu können, kann man für linearPos die Rest-Funktion benutzen. Die korrigierte Variante sieht also so aus: