Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 17:18:01
Projektplan-Tabelle oder Gantt ohne Balken
16.04.2024 17:05:25
formatierten Text direkt in Zelle einfügen
16.04.2024 16:47:14
Wörter in verschiedenen Zellen suchen und Wert zurückgeben
16.04.2024 16:22:58
Pivot
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
224to228
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
224to228
224to228
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Suche Formel

Suche Formel
26.02.2003 18:28:22
Karl Schmitz
Suche Formel die in einem Tabellenblatt sein soll. Wenn es geht ohne Makro.

Ich muss Provision berechnen.
Basis:

0 € Umsatz - 149.999,99 € keine Provision
150.000 kg Umsatz - 250.000 kg Umsatz erhält der Verkäufer 1,5 %
250.001 kg Umsatz- 350.000 kg Umsatz erhält der Verkäufer 2 %
über 350.001 kg Umsatz erhält der Verkäufer 2,25 %

Durchschnittsverkaufspreis 1,55411€ /kg

Jeder Wert wird innerhalb dieser Stufen separat in Ansatz gebracht.


Rechenweg zu "Fuß"

Beispiel:
Der Verkäufer hat 293.765 kg Umsatz gemacht, dies entspricht 456.546 € Umsatz = 1,55411 € /kg .


293.765 kg

minus 150.000 kg ( 0 € Umsatz - 149.999,99 € keine Provision)

= 143765 kg
100.000 kg =( kg zwischen 150.000 kg Umsatz - 250.000 kg Umsatz) 1,55411 €/kg x 1,5 % = 2331,165 €
43.765 kg =( kg zwischen 250.001 kg Umsatz- 350.000 kg Umsatz ) 1,55411 €/kg x 2,0 % = 1360,312 €
0 kg = (über 350.001 kg Umsatz 1,55411 €/kg x 2,25% = 0,000 €

Zu zahlende Provision gleich 3.691,477€

Wie kann ich dies in eine Formel bringen?
Ich stelle mir das so vor, dass ich die Umsatzwerte in die Zelle eingebe und sofort das Ergebnis habe.

Der Knackpunkt ist bei mir ist "Jeder Wert wird innerhalb dieser Stufen separat in Ansatz gebracht."

Für die Hilfe schon im voraus vielen Dank


25
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Re: Suche Formel
26.02.2003 19:22:45
Jörg Gradert

Hallo Franz,
das geht mit SVERWEIS, ich schicke dir mal ein Beispiel zu.
Teste es bitte ausführlich, falls ich die Aufgabenstellung nicht richtig verstanden haben sollte.

Gruß Jörg


... oder ...
26.02.2003 19:36:57
Volker Croll

... ein Weg ueber 3 Hilfsspalten, die man - falls sie die Ansicht stoeren - auch ausblenden kann.

Annahme: In B1 steht der Durchschnittsverkaufspreis 1,55411 und die 293.765 kg in A5.

Dann in die 1. Hilfspalte die Formel:
=WENN(A5>350000;((A5-350000)*2,25%)*$B$1;"")

in die 2. Hilfspalte die Formel:
=WENN(A5>250000;WENN(A5>350000;(100000*2%)*$B$1;(A5-250000)*2%)*$B$1;"")

in die 3. Hilfspalte die Formel:
=WENN(A5>150000;WENN(A5>250000;(100000*1,5%)*$B$1;((A5-250000)*1,5%)*$B$1);"")

Dann diese 3 Werte zusammenzaehlen.

Gruss Volker

Anzeige
Re: Suche Formel
26.02.2003 19:50:36
Jörg Gradert

Re: Suche Formel
26.02.2003 20:31:42
Jörg Gradert

Hallo Karl,
ich sehe gerade, dass die Provision nur für den über 150000 hinausgehenden Umsatz berechnet werden soll.
Dann muss die Formel folgendermassen abgeändert werden:
=(C2-150000)*1,55411*SVERWEIS(C2;A2:B5;2)

Gruß Jörg

... das fuehrt aber nicht zum gesuchten Ergebnis !
26.02.2003 20:54:07
Volker Croll

Hallo Joerg,

Du beruecksichtigst in Deiner Loesung nicht, dass der Prozentsatz nicht (!) linear ist - siehe die Beispielrechnung in der Frage.

Gruss Volker

Stimmt leider
26.02.2003 21:17:37
Jörg Gradert

Hallo Volker,
da habe ich wohl nicht genau genug gelesen, ich ging davon aus, dass der höhere Prozentsatz für den gesamten Umsatz gezahlt wird, wenn eine Stufe überschritten wird.
Dann taugt mein ganzer Ansatz bestimmt gar nichts.

Gruß Jörg

Anzeige
Re: Suche Formel
26.02.2003 21:37:42
Ralf Sögel

Hallo,
das liest sich wie eine Aufgabe aus dem Kurs "Excel für Controler- Einsteiger". Dein Ehrgeiz, das alles in eine Formel zu basteln, ist schon ok, aber völlig praxisfern.
Hinterlege in einem setupsheet die Provisionssätze und die Bewertungsgrenzen, gib ihnen aussagekräftige Namen und bau damit deine Formel auf. Das ist dann für den fall, dass sich die Werte ändern ein leichtes, das anzupassen.
Gruß Ralf

Das kann man auch anders sehen ...
26.02.2003 22:22:05
Volker Croll

Hallo Ralf,

warum nicht in meiner Formelloesung (s. weiter unten die Antwort "...oder...") noch die Grenzwerte - wie schon den Durchschnittsverkaufspreis - in Zellen schreiben und in den Formeln die Werte 150000, 250000 und 350000 durch die entsprechenden Zelladressen ersetzen?

Wozu ein extra Sheet?

Gruss Volker

Anzeige
Re: Das kann man auch anders sehen ...
26.02.2003 23:07:01
Ralf Sögel

Hallo,
das kannst du sehen wie du willst.
Werte, wie die Prozentsätze und die Ober/Untergrenzen gehören nicht in die Formel sondern in gesonderte Zellen.
Gruß Ralf

Sorry, aber da bin ich doch Deiner Meinung, ...
26.02.2003 23:10:09
Volker Croll

... habe doch nur gefragt, wozu ein extra Sheet?

Gruss Volker

Re: Sorry, aber da bin ich doch Deiner Meinung, ...
27.02.2003 00:05:07
Ralf Sögel

Hallo,
bei einem kleineren Projekt geht das natürlich auch innerhalb der Tabelle. Bei größeren Projekten(Beispiel das Excel- Add- Inn Solver), werden derartige Daten in einem Customizing- sheet hinterlegt, um z.B. bei Änderungen, dies an einer zentralen Position machen zu können. Das Ganze ist natürlich kein Muss.
Gruß Ralf

Anzeige
Zweiter Versuch
27.02.2003 05:53:43
Jörg Gradert

Re: ... oder ... auch falsch!
27.02.2003 06:16:43
Jörg Gradert

Hallo Volker,
gar nicht so einfach den Überblick zu behalten. Deine Lösung rechnet über 350000 zu viel aus.
Bei Umsatz >350000 läuft deine Hilfszelle 2 unverhältnismäßig hoch.

Gruß Jörg

kleiner Fehler noch!
27.02.2003 06:33:09
gradert

Hallo Karl,
die Formel in F2 soll richtig:

=SVERWEIS(E2;A2:C5;3)

heissen.

Jörg


Re: Suche Formel
27.02.2003 15:40:48
Karl Schmitz

Ich habe dies angeschaut und auch getestet. Es führt nicht zum richtigen Ergebnis.
Der Knackpunkt ist bei mir ist "Jeder Wert wird innerhalb dieser Stufen separat in Ansatz gebracht."

Der Verkäufer hat 293.765 kg Umsatz gemacht, dies entspricht 456.546 € Umsatz = 1,55411 € /kg .

293.765 kg

minus 150.000 kg ( 0 € Umsatz - 149.999,99 € keine Provision)

= 143765 kg

100.000 kg =( kg zwischen 150.000 kg Umsatz - 250.000 kg Umsatz) 1,55411 €/kg x 1,5 % = 2331,165 €
43.765 kg =( kg zwischen 250.001 kg Umsatz- 350.000 kg Umsatz ) 1,55411 €/kg x 2,0 % = 1360,312 €
0 kg = (über 350.001 kg Umsatz 1,55411 €/kg x 2,25% = 0,000 €

Zu zahlende Provision gleich 3.691,477€
Das ist wohl eine harte Nuß, aber ichfreue mich dass doch einige dran gehen.

Für die Hilfe schon im voraus vielen Dank

Gruß
Karl Schmitz



Anzeige
es fehlen nur 2 Klammern ...
27.02.2003 16:53:28
Volker Croll

... richtig ist:

=WENN(A5>250000;WENN(A5>350000;(100000*2%)*$B$1;((A5-250000)*2%)*$B$1);"")

Danke fuer den Hinweis, Joerg!

Gruss Volker

Re: Suche Formel
27.02.2003 16:55:57
Jörg Gradert

Hallo Karl,
hier als Formel:

=WENN($E$2>350000;($E$2-A5)*1,55411*0,0225+100000*1,55411*0,02+100000*1,55411*0,015;WENN($E$2>250000;($E$2-A4)*1,55411*0,02+100000*1,55411*0,015;WENN($E$2>150000;($E$2-A3)*1,55411*0,015;0)))

Gruß Jörg

Re: ... oder ...
27.02.2003 18:07:43
Karl Schmitz

Das ist der Falsche Rechenweg. Die Provisionswerte werden seperat je Stufe berechnet

Re: es fehlen nur 2 Klammern ...
27.02.2003 18:09:48
Karl Schmitz

Das ist der Falsche Rechenweg. Die Provisionswerte werden seperat je Stufe berechnet

Anzeige
... bei mir stimmen die Ergebnisse ...
27.02.2003 18:21:20
Volker Croll

Hallo Karl,

wieso falscher Rechenweg?

In den drei Hilfsspalten werden ja genau (Zitat:) "Die Provisionswerte ... seperat je Stufe berechnet" und dann zusammengezaehlt.
Das Ergebnis hat auch bei den von Dir vorgegebenen Beispielzahlen exakt gestimmt.

Wenn Du's in eine Formel packen willst, dann die Loesung Jörg Gradert (27.02.2003 - 16:55:57).

Gruss Volker

Re: ... bei mir stimmen die Ergebnisse ...
27.02.2003 18:38:32
Karl Schmitz

Ja, aber siehe bitte mein Rechenbeispiel mit Ergebnis. Es sind 3691,477€ Provision nicht wie die obige Formel hergibt 9165,029€

Gruss
Karl Schmitz


Sorry, aber dann machst Du etwas falsch ...
27.02.2003 20:02:42
Volker Croll

Hallo Karl,

wenn Du die Loesung nach den Vorgaben (d.h., was steht in welcher Zelle) von Joerg umsetzt, dann fuehrt seine Formel zum richtigen Wert. Hast Du wirklich die richtigen Grenzwerte in A3, A4 und A5 drinstehen?

Auch bei meiner Variante (unter Beruecksichtigung der von mir genannten Annahmen) kommt das richtige Ergebnis heraus.

Gruss Volker

Anzeige
@Karl @Volker
27.02.2003 21:57:59
Jörg Gradert

Hallo,
checkt doch bitte auch mal meine alles in einer Formel Lösung
Umsatzeingabe in E2
Habe ich schon vor einiger Zeit weiter oben eingegeben

=WENN($E$2>350000;($E$2-A5)*1,55411*0,0225+100000*1,55411*0,02+100000*1,55411*0,015;WENN($E$2>250000;($E$2-A4)*1,55411*0,02+100000*1,55411*0,015;WENN($E$2>150000;($E$2-A3)*1,55411*0,015;0)))

Gruß Jörg

Hier ist endlich die eine Formel!
27.02.2003 22:55:34
Jörg Gradert

Hallo,
jetzt endlich ohne Hilfszellen, Umsatz in E2 eingeben, Formel in Provisionsfeld einfügen. Volkers Lösung stimmt auch und hat meine Denkweise Probleme anzugehen erweitert.

=WENN($E$2>350000;($E$2-350000)*1,55411*0,0225+100000*1,55411*0,02+100000*1,55411*0,015;WENN($E$2>250000;($E$2-250000)*1,55411*0,02+100000*1,55411*0,015;WENN($E$2>150000;($E$2-150000)*1,55411*0,015;0)))

Vielen Dank für die gute Zusammenarbeit
Liebe Grüße Jörg

Anzeige
Re: Hier ist endlich die eine Formel!
28.02.2003 11:52:40
Karl Schmitz

Super, Super, Toll

Vielen Dank
Das war eine wirklich harte Nuß. Aber Du hast es doch geschafft.

Genau so habe ich mit das vorgestellt.
Nochmals vielen Dank.


Ich werde jetzt noch versuchen die "100000" variabel in die Formel einzubringen. Es kann auch mal sein, das diese Differenz unterschiedlich ist.

Gruß
Karl Schmitz

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige