Wie erstelle ich in Excel eine Formel für diese Gebührenstruktur?

Wie erstelle ich in Excel eine Formel für diese Gebührenstruktur?

Ich habe eine Zelle mit einem Wert A1 = 3,500,000.

Ich möchte für diese 3,5 Mio. $ eine Gebühr berechnen, die dieser Formel folgt:

7 % auf die erste Million, plus 5 % auf die zweite Million, plus 3 % auf die dritte Million, plus 2 % danach.

Idealerweise hätte ich es gerne, wenn dies alles in einer Zelle geschieht, also mit einer Formel in einer Zelle, und nicht mit einer ganzen Tabelle oder einem ganzen Arbeitsblatt.

Danke!

Antwort1

Es ist sicherlich möglich, dies mit einer einzigen Formel in einer einzigen Zelle zu lösen, wenn Sie jedoch Ihre Formeln leicht verständlich und leicht änderbar gestalten möchten, wäre eine Tabelle übersichtlicher.

--

Einzelzellenlösung

Wenn der Wert für eine Einzelzellenlösung im Bereich A1 liegt, berechnet diese Formel Ihre Staffelgebühren:

= A1 * 0,07 + MAX(A1-1000000,0) * -0,02 + MAX(A1-2000000,0) * -0,02 + MAX(A1-3000000,0) * -0,01

Sie multiplizieren den Gesamtwert mit 0,07 und ziehen dann für jede Stufe die Gebührendifferenz zwischen der neuen und der vorherigen Stufe ab (hierfür gibt es mehrere Möglichkeiten – ich habe nur ein Beispiel angegeben).

--

Einfache Tabellenlösung

Für eine einfache Tabellenlösung könnten Sie Folgendes tun:

Bildbeschreibung hier eingeben

Die Formeln zur Berechnung des Wertes in jeder Stufe:

E2: =MIN(C2,$A$1)

E3: =MIN(C3,$A$1)-SUM($E$2:E2)

E4: =MIN(C4,$A$1)-SUM($E$2:E3)

E5: =MAX($A$1-C4 ,0)

Die Formel zur Berechnung der Gesamtgebühr:

D6: =SUMMENPRODUKT(D2:D5,E2:E5)

Diese Konfiguration ist dynamisch. Sie können die Schwellenwerte oder die Gebühren ändern und die Formeln funktionieren weiterhin.


Wenn Sie in Zukunft möglicherweise zusätzliche Ebenen benötigen, empfiehlt sich eine XML-Tabelle. Wenn Sie immer nur 4 Ebenen verwenden, sich die Schwellenwerte und Gebühren für diese Ebenen jedoch ändern können, reicht die einfache Tabelle oben aus.

Antwort2

Man kann hierfür (ebenso wie für die sehr ähnlichen Steuerberechnungsprobleme) eine entsprechend strukturierte Nachschlagetabelle verwenden.

Von Ihrer Provisionstabelle

  • Spalte 1 = der Basisbetrag des Umsatzes
  • Spalte 2 = der Prozentsatz auf jeder Ebene
  • Spalte 3 = die Höhe der Provision, die vor dieser Stufe verdient wurde

In der folgenden Tabelle:
G1: 0
G2: =(E3-E2)*F2+G2
und füllen Sie sie so weit wie nötig aus

Dann mit Daten inA1

=(A1-VLOOKUP(A1,commissionTbl,1))*VLOOKUP(A1,commissionTbl,2)+VLOOKUP(A1,commissionTbl,3)

Bildbeschreibung hier eingeben

Beachten Sie, dass sich die Tabelle ganz einfach bearbeiten lässt, wenn sich Ihre Provisionsstruktur ändert – viel einfacher als das Bearbeiten von Formeln.

Antwort3

Ich möchte 3 Möglichkeiten aufzeigen.

Bildbeschreibung hier eingeben

  1. Berechnen Sie separat den Wert für Stufe 1, dann für Stufe 2, Stufe 3 und als Nächstes für Stufe 4 und ermitteln Sie schließlich die Gesamtsumme.

Überprüfen Sie dazu die Formel in den Zellen:

E2: =IF(D2<=1000000, D2*0.07, 1000000*0.07)

F2: =IF(D2>1000000, IF(D2<=2000000, (D21-1000000)*0.05, 1000000*0.05), 0)

G2: =IF(D2>2000000, IF(D2<=3000000, (D2-2000000)*0.03, 1000000*0.03), 0)

H2: =IF(D2>3000000, (D2-3000000)*0.02, 0)

Und das Letzte ist inIch 2: =SUM(E2:H2)


2. Eine andere Möglichkeit ist die Lösung mit einer einzigen Formel in I5.

Zelle I5:

=IF(D2<=1000000, D2*0.07, IF(D2<=2000000, 1000000*0.07 + (D2-1000000)*0.05, IF(D2<=3000000, 1000000*0.07 + 1000000*0.05 + (D2-2000000)*0.03, 1000000*0.07 + 1000000*0.05 + 1000000*0.03 + (D2-3000000)*0.02)))

3. Und die letzte Formel ist I7für die höhere Version von Excel, in der die LET-Funktionen verwendet wurden.

Zelle I7:

=LET( amount, D2,
        tier1, MIN(amount, 1000000),
        tier2, MAX(0, MIN(amount, 2000000) - 1000000),
        tier3, MAX(0, MIN(amount, 3000000) - 2000000),
        tier4, MAX(0, amount - 3000000),
        tier1*0.07 + tier2*0.05 + tier3*0.03 + tier4*0.02)

Achtung Passen Sie die Zellbezüge nach Bedarf an.

verwandte Informationen