Wie kann ich in Excel die Differenz zur nächsthöheren Note in Punkten berechnen?
Hallo,
ich lasse in Excel Noten mittels der SVerweis-Funktion aus einer Tabelle/Matrix auslesen. Diese zweispaltige Matrix enthält die jeweiligen Notengrenzen in Punkten und die entsprechende Note. Auf der Grundlage dieser Matrix würde ich gerne ermitteln, wie viele Punkte einem Prüfling zur nächsthöheren Note fehlen.
Das heißt: Wie kann ich Excel ausgehend von der aktuell erreichten Punktzahl (diese steht in Zelle H2) des jeweiligen Prüflings sagen, dass es aus der SVerweis-Matrix die Punktzahl der nächsthöhere Notengrenze heraussuchen und die Differenz in Punkten zurückgeben soll?
Vielen Dank für eure Hilfe! Die hilfreichste Antwort wird ausgezeichnet.
Viele Grüße
BerchGerch
2 Antworten
Ich hab das mal beispielhaft dargestellt
In A:B ist die Matrix. Die erreichten Punkte C2 und Note D2 kann man noch mit dem Sverweis ermitteln. Für die nächsthöhere Note benutze ich die mächtigere Lösung aus INDEX und VERGLEICH.
So lautet die Formel für die nächsthöhere Note in E2:
=INDEX(B2:B7;VERGLEICH(C2;A2:A7;1)+1)
(Man kann in diesem Fall natürlich auch einfach D2-1 rechnen, aber wenn der Notenvektor in Spalte B nicht linear verläuft oder sonstige Eigenarten hat, funktioniert die Formel trotzdem)
Die Differenzformel in F2 ist dann:
=INDEX(A2:A7;VERGLEICH(E2;B2:B7;0))-C2
Vielen herzlichen Dank! Das klappt einwandfrei! So habe ich mir das vorgestellt ;-)
Die Differenz zu welchem Wert genau möchtest du ermitteln?
Wenn wir vom Zahlenbeispiel in meinem Bild ausgehen, dann kann die Punkte-Differenz zur nächstniedrigeren Note irgendwas zwischen 14 und 38 sein
88-14=74
88-38=50
Das liegt dann beides im Bereich von Note 4.
Die Differenz zur Untergrenze der nächstniedrigeren Note wäre
=C2 - INDEX(A2:A7;VERGLEICH(C2;A2:A7;1)-1)
Die Differenz zur Obergrenze (74) wäre, wenn man davon ausgeht, dass die Punktzahlen ganzzahlig sind:
=C2 - INDEX(A2:A7;VERGLEICH(D2;B2:B7;0) + 1
(alles ungetestet)
Hinweis: Falls die erreichten Noten die höchsten oder niedrigsten (1 oder 6) sind, verweisen ja die Formeln zur nächsthöheren oder -niedrigeren Note ja immer noch auf die Zellen danach oder davor. Das könnte sinnlose Ergebnise hervorrufen. Das könnte man dann z.B. durch ein WENN abfangen.
Die Differenz zur Obergrenze wäre, wenn man davon ausgeht, dass die Punktzahlen ganzzahlig sind: (...)
Wie wären die Formeln für die Differenzen nach oben und unten denn, wenn die Notengrenzen manchmal auch Halbpunkte einschließen würden?
Du ermittelst zunächst die Note, die der aktuellen Punktezahl entspricht. Diese Note minus 1 benutzt du als INDEX um aus der Spalte mit den Punkten sie nächsthöhere Punkteanzahl auszulesen. Dies minus H2 ist deine gewünschte Differenz.
Hallo DeeDee07,
vielleicht könntest du mir nochmal helfen: Wie wäre denn die obige Differenzformel, wenn ich die Differenz zur nächstniedrigeren Note ausrechnen will?
Die Formel für die nächstniedrigere Note habe ich bereits auf
=INDEX(B2:B7;VERGLEICH(C2;A2:A7;1)-1)
geändert, aber bei der Differenzformel komme ich nicht weiter. Wäre toll, wenn du mir da nochmal helfen könntest. Vielen Dank und viele Grüße
BerchGerch