Office Forum
www.Office-Loesung.de
Access :: Excel :: Outlook :: PowerPoint :: Word :: Office :: Wieder Online ---> provisorisches Office Forum <-
Preisstaffelung berechnen mit Rabatt
zurück: Zelleninhalt in 0,25 - Schritten runden weiter: Mit Summeprodukt einen Text finden Unbeantwortete Beiträge anzeigen
Neues Thema eröffnen   Neue Antwort erstellen     Status: Feedback Facebook-Likes Diese Seite Freunden empfehlen
Zu Browser-Favoriten hinzufügen
Autor Nachricht
schlunzzie
Neuling


Verfasst am:
02. Sep 2009, 16:20
Rufname:

Preisstaffelung berechnen mit Rabatt - Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo Zusammen,

ich möchte folgende Preisstaffelung in Excel berechnen und komme nicht so richtig weiter und bin um jede Hilfe dankbar.

1 to 50 000 21,40 €
50 001 to 100 000 20,97 € 2%
100 001 to 150 000 20,54 € 4%
150 001 to 200 000 20,12 € 6%
200 001 to 250 000 19,69 € 8%
250 001 to 300 000 19,26 € 10%
300 001 to 400 000 18,83 € 12%
400 001 to 500 000 18,40 € 14%
> 500 000 17,98 € 16%

Also wenn man ein Volumen von z.B. 220.000 Stück eingibt, soll wie folgt berechnet werden:
Anzahl Rabatt Betrag
50.000 21,40 € 1.070.000,00 €
49.999 20,97 € 1.048.579,03 €
49.999 20,54 € 1.027.179,46 €
49.999 20,12 € 1.005.779,88 €
20.003 19,69 € 393.819,06 €

SUM 220.000 4.545.357,43 €/220.000Stk=20,66 EURO

D.h. die unterschiedlichen Rabatte sollen in den Gesamtdurchschnittspreis miteinfließen. Ich habe es mit WENN-Bedingungen versucht bin aber leider bis jetzt nicht weitergekommen. An einer Darstellung mit SVERWEIS bin ich leider gescheitert.
Über eine Antwort oder Hilfetstellung würde ich mich freuen.
LG schlunzzie
Zero
Formeln, Diagramme und ein bisschen VBA


Verfasst am:
02. Sep 2009, 17:18
Rufname: Dennis
Wohnort: NDS


AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo

Hier mal eine Variante per UDF.

 ABCDEF
1abPreis MengePreisØ
2021,40 € 220.0004.545.301,71 €20,66 €
350.00120,97 €    
4100.00120,54 €    
5150.00120,12 €    
6200.00119,69 €    
7250.00119,26 €    
8300.00118,83 €    
9400.00118,40 €    
10500.00117,98 €    

ZelleFormel
E2=staffelpreis(D2;$A$2:$B$10)
F2=E2/D2


Der Code kommt in ein allgemenes Modul.
Code:
Function Staffelpreis(Menge As Long, Staffelpreismatrix As Range) As Double

Dim arrStaffel
Dim i As Long

arrStaffel = Staffelpreismatrix
For i = 1 To Menge
    Staffelpreis = Staffelpreis + WorksheetFunction.VLookup(i - 1, Staffelpreismatrix, 2, 1)
    Next
   
End Function

_________________
Gruß zero
marinade
Gast


Verfasst am:
02. Sep 2009, 17:44
Rufname:

AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo,

durch den gestaffelten Rabatt wirds aber teurer für den Kunden. :(

Hier eine reine Formelvariante:
 ABCDEFG
20    normaler Rabatt220.0004.331.360,00 €
21    gestaffelt220.0004.545.357,43 €
22    4.545.357  
23050.00021,40 € 1.070.000  
2450.001100.00020,97 €2%1.048.579  
25100.001150.00020,54 €4%1.027.179  
26150.001200.00020,12 €6%1.005.780  
27200.001250.00019,69 €8%393.819  
28250.001300.00019,26 €10%   
29300.001400.00018,83 €12%   
30400.001500.00018,40 €14%   
31500.0011.000.00017,98 €16%   

Formeln der Tabelle
ZelleFormel
G20=F20*INDEX(C23:C31;VERGLEICH(F20;A23:A31;1))
G21{=SUMME((B23:B31<F21)*((B23:B31-A23:A31)*C23:C31))+(F21-SUMME((B23:B31<F21)*(MAX(WENN(F21>B23:B31;B23:B31))-B23:B31-A23:A31))-MAX(WENN(F21>B23:B31;B23:B31)))*INDEX(C23:C31;MIN(WENN(B23:B31>F21;ZEILE(1:9))))}
E22=SUMME(E23:E31)
E23=WENN(B23<F$21;(B23-A23)*C23;WENN(F$21-B21<0;"";(F$21-B21+ZEILE(A1)-2)*C23))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Besten Gruß
marinade
Gast


Verfasst am:
02. Sep 2009, 17:47
Rufname:

AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo Zero,

einer von uns beiden hat sich verrechnet? Meine Daten entsprechen dem Fragesteller. Bei dir wirds etwas billiger.

Lieben Gruß
marinade
Gast


Verfasst am:
02. Sep 2009, 18:20
Rufname:


AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

edit, weil ich das = unterschlagen habe.

so muss die Formel lauten für Zelle G21
Code:
=SUMME((B23:B31<=F21)*((B23:B31-A23:A31)*C23:C31))+(F21-SUMME((B23:B31<=F21)*(MAX(WENN(F21>=B23:B31;B23:B31))-B23:B31-A23:A31))-MAX(WENN(F21>=B23:B31;B23:B31)))*INDEX(C23:C31;MIN(WENN(B23:B31>=F21;ZEILE(1:9))))

STRG+Shift+Enter

Zelle E23
Code:
=WENN(B23<F$21;(B23-A23)*C23;WENN(F$21-B22<=0;"";(F$21-B22+ZEILE(A1)-2)*C23))

Enter und runter ziehen

Zellformat natürlich mit 2 Nachkommastellen.

Besten Gruß
Gast



Verfasst am:
02. Sep 2009, 18:31
Rufname:

AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo zusammen,

geht einfach mal davon aus, dass der Fragesteller falsch gerechnet hat:
Zitat:
50.000 21,40 € 1.070.000,00 €
49.999 20,97 € 1.048.579,03 €
49.999 20,54 € 1.027.179,46 €
49.999 20,12 € 1.005.779,88 €
20.003 19,69 € 393.819,06 €

müsste eigentlich so lauten:
50.000 21,40 € 1.070.000,00 €
50.000 20,97 € 1.048.500,00 €
50.000 20,54 € 1.027.000,00 €
50.000 20,12 € 1.006.000,00 €
20.000 19,69 € 393.800,00 € --> 4545300,00€

Gruß
Elg
marinade
Gast


Verfasst am:
02. Sep 2009, 19:55
Rufname:

AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo Elg,

schön von dir zu lesen. Sicherlich hast du recht und die Staffelung sollte nach deinem Bsp. geordnet sein. Komischer Weise habe ich trotzdem andere Ergebnisse. Witzig. :D

Wenn du magst, schau mal.
 ABCDE
20  normaler Rabatt220.0004.331.360,00 €
21  gestaffelt220.0004.545.360,00 €
22    4.545.360,00 €
23150.00021,40 € 1.070.000,00 €
2450.001100.00020,97 €2%1.048.600,00 €
25100.001150.00020,54 €4%1.027.200,00 €
26150.001200.00020,12 €6%1.005.800,00 €
27200.001250.00019,69 €8%393.760,00 €
28250.001300.00019,26 €10% 
29300.001400.00018,83 €12% 
30400.001500.00018,40 €14% 
31500.0011.000.00017,98 €16% 

Formeln der Tabelle
ZelleFormel
E21{=SUMME((B23:B31<=D21)*(50000*C23:C31))+(D21-MAX(WENN(B23:B31<=D21;B23:B31)))*INDEX(C23:C31;MAX(WENN(B23:B31<=D21;ZEILE(1:9)))+1)}
E22=SUMME(E23:E31)
E23=WENN(D$21>=B23;B$23*C23;WENN(D$21-B22<=0;"";(D$21-B22)*C23))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Lieben Gruß
Gast



Verfasst am:
02. Sep 2009, 20:34
Rufname:

AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo,

hier die Ursache:

Meine Berechnung:
Tabelle5

 ABCDE
14 normaler Rabatt2200004.331.360,00 €
15 gestaffelt2200004.545.360,00 €
16 0 4.545.360,00 €
17150.00021,40 € 1.070.000,00 €
1850.001100.00020,97 €2%1.048.500,00 €
19100.001150.00020,54 €4%1.027.000,00 €
20150.001200.00020,12 €6%1.006.000,00 €
21200.001250.00019,69 €8%393.800,00 €
22250.001300.00019,26 €10%
23300.001400.00018,83 €12%
24400.001500.00018,40 €14%
25500.0011.000.00017,98 €16%

Formeln der Tabelle
ZelleFormel
C18=RUNDEN($C$4*(1-D18);2)
C19=RUNDEN($C$4*(1-D19);2)


Tabelle5

 E
171.070.000,00 €
181.048.500,00 €
191.027.000,00 €
201.006.000,00 €
21393.800,00 €

Formeln der Tabelle
ZelleFormel
E17=(B17-B16)*C17
E18=(B18-B17)*C18
E19=(B19-B18)*C19
E20=(B20-B19)*C20
E21=(D15-B20)*C21


Excel Jeanie Html
gleiche Berechnung nach Deiner Methode:
Tabelle5

 ABCDE
1 normaler Rabatt2200004.331.360,00 €
2 gestaffelt2200004.545.360,00 €
3 0 4.545.360,00 €
4150.00021,40 € 1.070.000,00 €
550.001100.00020,97 €2%1.048.600,00 €
6100.001150.00020,54 €4%1.027.200,00 €
7150.001200.00020,12 €6%1.005.800,00 €
8200.001250.00019,69 €8%393.760,00 €
9250.001300.00019,26 €10%
10300.001400.00018,83 €12%
11400.001500.00018,40 €14%
12500.0011.000.00017,98 €16%

Formeln der Tabelle
ZelleFormel
C5=$C$4*(1-D5)
C6=$C$4*(1-D6)
C7=$C$4*(1-D7)


Tabelle5

 E
41.070.000,00 €
51.048.600,00 €
61.027.200,00 €
71.005.800,00 €
8393.760,00 €

Formeln der Tabelle
ZelleFormel
E4=(B4-B3)*C4
E5=(B5-B4)*C5
E6=(B6-B5)*C6
E7=(B7-B6)*C7
E8=(D2-B7)*C8


Excel Jeanie Html

Was ein paar Nachkommastellen ausmachen, wenn es um Multiplikationen im Bereich vo 10^5 und mehr geht.

Gruß

Elg
marinade
Gast


Verfasst am:
02. Sep 2009, 22:19
Rufname:

AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo Elg,

Zitat:
Was ein paar Nachkommastellen ausmachen, wenn es um Multiplikationen im Bereich vo 10^5 und mehr geht.


jupp, ist wohl war.

Am besten für den Kunden, wenn Zeros Variante zum Einsatz kommt. Ist wohl die billigste, wenn ich richtig geguckt habe. Very Happy

Besten Gruß
Gast



Verfasst am:
03. Sep 2009, 14:54
Rufname:


AW: Preisstaffelung berechnen mit Rabatt - AW: Preisstaffelung berechnen mit Rabatt

Nach oben
       Version: Office 2003

Hallo Zusammen und vielen Dank für diese ausführlichen und sehr hilfreichen Anregungen. Habe zum ersten Mal hier gepostet und bin ja kann man sagen überwältigt von der schnellen und guten Resonanz.....super Forum !!!
Werde die Formeln jetzt noch mal testet und Feedback geben welche final zum Einsatz kommt.

Grüße
schlunzzie
Neues Thema eröffnen   Neue Antwort erstellen Alle Zeiten sind
GMT + 1 Stunde

Diese Seite Freunden empfehlen

Seite 1 von 1
Gehe zu:  
Du kannst Beiträge in dieses Forum schreiben.
Du kannst auf Beiträge in diesem Forum antworten.
Du kannst deine Beiträge in diesem Forum nicht bearbeiten.
Du kannst deine Beiträge in diesem Forum nicht löschen.
Du kannst an Umfragen in diesem Forum nicht mitmachen.
Du kannst Dateien in diesem Forum nicht posten
Du kannst Dateien in diesem Forum herunterladen

Verwandte Themen
Forum / Themen   Antworten   Autor   Aufrufe   Letzter Beitrag 
Keine neuen Beiträge Excel Formeln: Blutdrucktabelle. Werte aus mehreren Eingabef. berechnen 4 houlot 27776 03. Apr 2006, 22:01
Gast Blutdrucktabelle. Werte aus mehreren Eingabef. berechnen
Keine neuen Beiträge Excel Formeln: SUMME einer Zeile mit VBA berechnen 7 Klausi 10623 27. Feb 2006, 14:10
Klausi SUMME einer Zeile mit VBA berechnen
Keine neuen Beiträge Excel Formeln: Abwesenheit in ganzen Stunden berechnen 0 huajai 2141 24. Feb 2006, 04:15
huajai Abwesenheit in ganzen Stunden berechnen
Keine neuen Beiträge Excel Formeln: Eine von zwei Zellen eingeben, andere berechnen 5 elf99 1010 10. Feb 2006, 11:42
fridgenep Eine von zwei Zellen eingeben, andere berechnen
Keine neuen Beiträge Excel Formeln: Datum Tag/Nacht berechnen 5 Ralf_42 2036 19. Nov 2005, 11:38
rainberg Datum Tag/Nacht berechnen
Keine neuen Beiträge Excel Formeln: Tage berechnen 5 Couri 1440 27. Sep 2005, 08:10
Couri Tage berechnen
Keine neuen Beiträge Excel Formeln: Arbeitszeiten und Überstunden berechnen 6 d.brockmoeller 3885 18. Aug 2005, 20:32
d.brockmoeller Arbeitszeiten und Überstunden berechnen
Keine neuen Beiträge Excel Formeln: Preise berechnen 2 excelflasche 1010 18. Mai 2005, 08:19
excelflasche Preise berechnen
Keine neuen Beiträge Excel Formeln: Nachtzulage Berechnen 2 Fuda74 8516 04. Apr 2005, 11:02
Fuda74 Nachtzulage Berechnen
Keine neuen Beiträge Excel Formeln: Zeitdauer abzüglich Nachtzeit und Wochenende berechnen 1 sia 2216 19. Feb 2005, 13:59
Jeanie Zeitdauer abzüglich Nachtzeit und Wochenende berechnen
Keine neuen Beiträge Excel Formeln: Alter berechnen - Excel Version 97 11 Sita 6078 15. Feb 2005, 14:42
Sita Alter berechnen - Excel Version 97
Keine neuen Beiträge Excel Formeln: Alter berechnen!! 1 Gast 7024 23. Jan 2005, 22:57
Tom11 Alter berechnen!!
 

----> Diese Seite Freunden empfehlen <------ Impressum - Besuchen Sie auch: Excel Tipps