Frage von dreamboy1997, 44

Excel wert je nach zeit und Kilometer hinzurechnen?

Hallo wie ist es möglich einen bestimmten Wert zu sein Ergebnis hinzuzufügen. Beispiel von 7-23 Uhr kostet eine Stunde 2,50 und von 0-6 Zahlt man nur 0,50. Für einen Kilometer zahlt man 0-100 0,22 jeder weitere 0,18

Habe es versuchen alle möglichenkeiten die mir einfallen zu verwenden. Leider hat keine funktioniert.

Lg

Expertenantwort
von Ninombre, Community-Experte für Excel, 10

So neue Antwort mit Bild und etwas systematischer aufgebaut.
Vielleicht fällt einem noch eine einfachere Formel ein - wenn nicht sollte man auf jeden Fall versuchen, nicht zuviele Variablen (wie Preise) direkt in die Formel zu packen. Sonst werden spätere Änderungen richtig kompliziert.

Ich kann leider keine fertige Datei ins Internet stellen, daher Bild und Formeln zum reinkopieren.

Fahrtdaten  und die Einstellungen (Beginn der teurer Zeiten, Preise ...) trägst Du einfach als feste Werte ein. Dabei nur auf die Zellformate achten: Uhrzeit, Zahl und Währung

F12: (Dauer Tagtarif)

=MAX(;1-MAX(B2;F2)+MAX(;F3-B2)+MIN(B3;F3)+(B3>F2)*(B3-F2)-(B2<B3)*(1-(F2-F3))-(F2<F3)*(B3+(B2>B3)-B2))*(B2<>B3)*(F2<>F3)

F13 (Dauer Nachttarif)

=REST(B3-B2;1)-MAX(;1-MAX(B2;F2)+MAX(;F3-B2)+MIN(B3;F3)+(B3>F2)*(B3-F2)-(B2<B3)*(1-(F2-F3))-(F2<F3)*(B3+(B2>B3)-B2))*(B2<>B3)*(F2<>F3)

F14 (Kilometer bis zum Grenzwert)
=MIN(100;B4)

F15 (Zusätzliche Kilometer)
=MAX(B4-100;0)

Die Aufteilung der Kilometer bis 100 und darüber hinaus könnte man sich ggf. auch sparen, aber so ist es noch eindeutiger nachzuvollziehen.

G12 (Kosten Tagtarif)
=F12*24*F4    -> an der Stelle wichtig: Excel zeigt beim Format Uhrzeit zwar "12:00" an, rechnet aber intern mit Dezimalzahlen. 1 Tag = 24h. Daher ist eine Stunde 1/24. 12 Stunden wäre also 12/24 = 0,5 nach Excellogik. Durch das Multiplizieren mit 24 erhält man den Wert in "richtigen" Stunden

G13 (Kosten Nacht)
=F13*24*F5

G14 (Kosten km bis 100)
=F14*F7

G15 (Kosten zusätzliche KM)
=F15*F8

Der Gesamtpreis in B6 ist dann einfach die Summe
=SUMME(G12:G15)

Antwort
von agent888, 22

Mit einer wenn-funktion.
Sieh dir dieses Video an...

Expertenantwort
von Ninombre, Community-Experte für Excel, 20

Was sind denn die Parameter für die Berechnung? Eine Zeitdauer mit Beginn und Ende sowie eine Entfernung?

Kommentar von dreamboy1997 ,

Beispiel man Fahrt von 20 - 6 Uhr weg, dann sind es ja 2 Preis Kategorien. 7-23 wird dann 2,50 €berechnet und 0-6 0,5€. Es sind 120 Kilometer beispielsweise dann ist es für Kilometer 1-100 0,22 jeder weiterer 0.18

Kommentar von Ninombre ,

Da hast Du natürlich auch ein etwas komplizierteres Problem - neben dem Abgrenzen der Zeiträume nach Tag- / Nachttarif kommt noch das Problem mit Zeiten, die über Mitternacht hinaus gehen.

Das musste ich mir aus dem Internet auch etwas zusammenklauben

Versuch den Aufbau mal wie hier beschrieben:
http://www.excelformeln.de/formeln.html?welcher=9

A1 = Beginn der teuren Zeit (7.00)
B1 = Ende der teuren Zeit (00:00)

In A2 der Beginn der Fahrt und B2 das Ende.
C2 ist die Anzahl der Kilometer

In eine Zelle Deiner Wahl dann die Formel:


=MAX(;1-MAX(A2;A1)+MAX(;B1-A2)+MIN(B2;B1)+(B2>A1)*(B2-A1)-(A2<B2)*(1-(A1-B1))-(A1<B1)*(B2+(A2>B2)-A2))*(A2<>B2)*(A1<>B1)*24*2,5+(REST(B2-A2;1)-MAX(;1-MAX(A2;A1)+MAX(;B1-A2)+MIN(B2;B1)+(B2>A1)*(B2-A1)-(A2<B2)*(1-(A1-B1))-(A1<B1)*(B2+(A2>B2)-A2))*(A2<>B2)*(A1<>B1))*24*0,5+WENN(C2>100;(C2-100)*0,18+100*0,22;C2*0,22)
ja, die ist lächerlich lang:
Das komplizierte (s. Link) ist das Ermitteln, wieviel von der Gesamtdauer in den teuren Tarif fällt. Gesamtzeit - teurer Tarif = billiger Tarif.
Kommentar von dreamboy1997 ,

Ist es echt so kompliziert?

Kommentar von Ninombre ,

Ratsam wäre es vielleicht Hilfsspalten anzulegen, damit es etwas übersichtlicher bleibt. Ich schreibe eine neue Antwort, dann kann ich ein Bild dranhängen.

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten