Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1468to1472
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
Inhaltsverzeichnis

Fromel mit Bedingung

Fromel mit Bedingung
29.01.2016 06:03:38
Bernd
Liebe Formelprofis,
habe zwar schon selbst herumgetüftelt, komme aber auf keine Lösung da mein Excelwissen nicht ausreichend ist.
Ich hoffe daher, es kann mir hier jemand weiter helfen denn ich suche eine Formel mit gewissen Kriterien.
Also,
in A1 steht der Wert 0,20 für 0-50 km
in A2 steht der Wert 0,10 für 51-250 km
in A1 steht der Wert 0,005 ab 250km
In die Zelle A5 wird dann ein Wert eingegeben. Nun benötige ich eine Formel welche folgendes berechnet:
Wenn ich z.B.: in A5 264 eingebe muss als Ergebnis 44,60 heraus kommen. Excel muss dabei immer so rechnen wie oben angeführt, also zuerst die 0,20 mal 50km, + 0,10 mal 250 km + 0,05 mal 3 km.
Wichtig ist dabei die Reihenfolge, also von oben nach unten.
Was dann noch wichtig ist, es darf ein maximaler Wert von 52,00 herauskommen, dies müsste man in der Formel auch berücksichtige.
Ich hoffe ich habe mich verständlich ausgedrückt und denke, dass es hier nicht nötig ist, eine Beispielmappe hoch zu laden.
Ich Danke jedem der mir hilft und wünsche einen schönen Freitag.
Gruß Bernd
PS.: Kann sein das ich erst morgen antworte!

26
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Fromel mit Bedingung
29.01.2016 06:47:32
otto
Moin,
wieso 0,05 mal 3 km?
otto

AW: Fromel mit Bedingung
29.01.2016 08:29:48
hary
Moin
Deine Vorgabe passt doch nicht ganz oder?
"also zuerst die 0,20 mal 50km, + 0,10 mal 250 km + 0,05 mal 3 km."



Tabelle2
 ABCDE
10,2bis 50 Testrechnung 
20,1bis 250 KmKosten
30,005ueber 250 5010
4   20020
5264Km 140,07
6     
730,07Kosten   

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D3=MIN(50;A5)  =MIN(50,R[2]C[-3])
D4=MAX(A5-50;0)-MAX(A5-250;0)  =MAX(R[1]C[-3]-50,0)-MAX(R[1]C[-3]-250,0)
D5=MAX(A5-250;0)  =MAX(RC[-3]-250,0)
E3:E5=D3*A1  =RC[-1]*R[-2]C[-4]
A7=(MIN(50;A5)*A1)+((MAX(A5-50;0)-MAX(A5-250;0))*A2)+(MAX(A5-250;0)*A3)  =(MIN(50,R[-2]C)*R[-6]C)+((MAX(R[-2]C-50,0)-MAX(R[-2]C-250,0))*R[-5]C)+(MAX(R[-2]C-250,0)*R[-4]C)

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary

Anzeige
AW: oh, sorry zu spät ... allerdings ...
29.01.2016 08:37:05
...
Hallo hary,
... fehlt bei Dir noch die max Begrenzung auf 52.
Gruß Werner
.. , - ...

AW: da stimmt einiges nicht ...
29.01.2016 08:35:21
...
Hallo Bernd,
... so wie Du es beschrieben hast, würde folgende Formel zu dem Ergebnis gelangen:
=MIN(MAX(A5-250;0)*A3+(MAX(MIN(A5;250);0)-50)*A2+MIN(A5;50)*A1;52)
Allerdings ergibt das bei mir 30,07 und nicht wie von Dir angeben 44,6
Du Solltest Deine Bedingungen nochmal prüfen.
Gruß Werner
.. , - ...

AW: da stimmt einiges nicht ...
29.01.2016 08:41:14
...
Hallo Bernd,
... so wie Du es beschrieben hast, würde folgende Formel zu dem Ergebnis gelangen:
=MIN(MAX(A5-250;0)*A3+(MAX(MIN(A5;250);0)-50)*A2+MIN(A5;50)*A1;52)
Allerdings ergibt das bei mir 30,07 und nicht wie von Dir angeben 44,6
Du Solltest Deine Bedingungen nochmal prüfen.
Gruß Werner
.. , - ...

Anzeige
AW: sorry für versehentl. Doppelposting :-( owT
29.01.2016 08:46:29
...
Gruß Werner
.. , - ...

AW:@Werner
29.01.2016 08:50:07
hary
Moin Werner
Du bist der Formelprofi, deshalb mal bitte zum Verstaendniss: Warum 52 ?



Tabelle2
 ABCDE
10,2bis 50 Testrechnung 
20,1bis 250 KmKosten
30,005ueber 250 499,8
4   00
549Km 00
6     
79,8Kosten   
89,7    
9     
109,8Gegenrechnung   

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D3=MIN(50;A5)  =MIN(50,R[2]C[-3])
D4=MAX(A5-50;0)-MAX(A5-250;0)  =MAX(R[1]C[-3]-50,0)-MAX(R[1]C[-3]-250,0)
D5=MAX(A5-250;0)  =MAX(RC[-3]-250,0)
E3:E5=D3*A1  =RC[-1]*R[-2]C[-4]
A7=(MIN(50;A5)*A1)+((MAX(A5-50;0)-MAX(A5-250;0))*A2)+(MAX(A5-250;0)*A3)  =(MIN(50,R[-2]C)*R[-6]C)+((MAX(R[-2]C-50,0)-MAX(R[-2]C-250,0))*R[-5]C)+(MAX(R[-2]C-250,0)*R[-4]C)
A8=MIN(MAX(A5-250;0)*A3+(MAX(MIN(A5;250);0)-50)*A2+MIN(A5;50)*A1;52)  =MIN(MAX(R[-3]C-250,0)*R[-5]C+(MAX(MIN(R[-3]C,250),0)-50)*R[-6]C+MIN(R[-3]C,50)*R[-7]C,52)
A10=A5*A1  =R[-5]C*R[-9]C

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary

Anzeige
AW: hast Du möglicherweise nur überlesen ...
29.01.2016 08:53:18
...
Hallo hary,
... Bernd schrieb auch noch: "Was dann noch wichtig ist, es darf ein maximaler Wert von 52,00 herauskommen, dies müsste man in der Formel auch berücksichtige"
Gruß Werner
.. , - ...

AW: hast Du möglicherweise nur überlesen ...
29.01.2016 09:00:53
hary
Moin Werner
Ok, mit dem Maxbetrag hab ich nicht drin, aber mit F9-Taste kommt bei deiner Formel raus:
=MIN(0+-0,1+9,8;52)
das minus 0,1 passt doch nicht!?
gruss hary

AW: da hast Du völlig Recht ...
29.01.2016 09:12:38
...
Hallo hary,
... ein Schreibfehler richtiger in meinem Fall eine "Umstellungsfehler".
Ich hätte die Formel also mal prüfen sollen.
Es sollte natürlich: =MIN(MAX(A5-250;0)*A3+(MAX(MIN(A5;250)-50;0))*A2+MIN(A5;50)*A1;52) lauten.
Danke für Deinen Hinweis.
Gruß Werner
.. , - ...

Anzeige
AW: Alles klar,danke gruss owT
29.01.2016 09:21:43
hary
.

AW: Formel liefert falsches Ergebnis
29.01.2016 16:48:35
Bernd
Hallo an Alle,
recht herzlichen Dank für die rege Teilnahme bzw. Vorschläge für die Lösung.
Ich habe nun die Formel vom Werner versucht.
Ja, Du hattest recht Werner, ich habe da meine Anfrage total falsch gestellt. Vermutlich ist der Grund dafür jener, dass ich es habe versucht zu Beschreiben, da sieht man wieder, eine Beispieldatei ist doch von großem Vorteil.
Leider liefert die Formel dennoch einen falschen Wert. Habe eine Beispielmappe mal hochgeladen:
https://www.herber.de/bbs/user/103148.xlsx
In der Datei sind dann zwei unterschiedliche Beispiele mit der jeweiligen Gegenrechnung.
Da sollte dann entweder 31,40 oder 35,15 rauskommen, mit der Formel ist der Wert jedoch ein anderer.
Berechnet werden sollte so:
0,20 für 0-50 km
0,10 für 51-250 km
0,005 ab 250km
Also erst ab erreichten 300km darf der Wert je km mit 0,05 gerechnet werden.
Ich selber finde da aber nun auch keine Lösung woran der Fehler liegt.
Ich hoffe es sieht sich mein Problem nochmal wer an, und kann mir sagen, was da falsch ist bzw. was ich da nicht richtig mache.
Dennoch, nochmals recht herzlichen Dank Euch allen, schönen Freitag Abend noch und lg,
Bernd

Anzeige
AW: was gilt denn wirklich ? ...
29.01.2016 20:09:44
...
Hallo Bernd,
... bisher und auch jetzt schreibst Du: in A1 (Du meinst A3) steht der Wert 0,005 ab 250km im weiteren Text hier schreibst Du jedoch: "Also erst ab erreichten 300km darf der Wert je km mit 0,05 gerechnet werden." Und was gilt dann zwischen 251 bis 300 km?
Du solltest schon klare und eindeutige Bedingungen angeben.
Gruß Werner
.. , - ...

AW: was gilt denn wirklich ? ...
29.01.2016 21:21:58
Bernd
Hallo Werner,
tut mir wirklich Leid wenn ich es vielleicht zu umständlich erklärt habe.
Es ist so gemeint:
Wenn ich 264 km fahre dann bekomme ich für die ersten 50km Euro 0,20, für die km von 51 - 264 wären es 0,10, bei diesem Beispiel werden die 300 erst gar nicht erreicht.
Fahre ich aber 303 km, dann bekomme ich für die ersten 50km Euro 0,20, von km 51-300 0,10 und ab dem km 301 jedoch nur mehr 0,05.
Es ist also ein gestaffelter Betrag der berechnet werden soll.
Also es gibt immer eine gesamte Wegstrecke die eben mit den unterschiedlichen Beträgen, jedoch maximal mit Euro 52, berechnet werden sollte.
Ich dachte meine Beispieldatei veranschaulicht das, habe mich wohl geirrt, sorry dafür.
Hoffe es klapp nun und Danke Dir für die Hilfe.
lg Bernd

Anzeige
AW: was gilt denn wirklich ? ...
30.01.2016 08:16:16
hary
Moin Bernd
Laut deiner jetzigen Beschreibung:
Bis 50 0,20€
51 bis 300 0,10€
ab 301 0,05€
dann tausch die 250 einfach gegen 300 aus.



Tabelle1
 ABC
12303 35,15

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
C12=MIN(MAX(A12-300;0)*A3+(MAX(MIN(A12;300)-50;0))*A2+MIN(A12;50)*A1;52)  =MIN(MAX(RC[-2]-300,0)*R[-9]C[-2]+(MAX(MIN(RC[-2],300)-50,0))*R[-10]C[-2]+MIN(RC[-2],50)*R[-11]C[-2],52)

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary

Anzeige
AW: was gilt denn wirklich ? ...
30.01.2016 13:58:45
Bernd
Hallo Harry,
muss sagen, mhhhhh, denn nun kommt mit Deiner Formel bei 264 und 303 km das selbe Ergebnis raus.
Anscheinend ist mein einfaches Beispiel, doch zu komplex?
Und leider weiß ich nun überhaupt nicht, was ich da noch erklären sollte, es ist ja alles leicht und verständlich gesagt, finde ich.
Es gibt für eine Wegestrecke immer denselben Tarif, für die ersten 50km 0,20 Euro, danach bis zum Kilometer 264, bekomme ich 0,10 Euro, also für zuzüglich 214 km. Und 214 plus 50 sind eben nun mal nur 264 und erreichen die 300er Grenze gar nicht. Würde dies der Fall sein, so wären es dann je km 0,05 Euro.
Naja, trotzdem Thx an alle Helfer und schönes WE noch,
lg Bernd

Anzeige
AW: was gilt denn wirklich ? ...
30.01.2016 16:13:55
Josef
Hallo Bernd
Ist bei dir in A1 bis A3 mit den Staffelpreisen etwas schief gelaufen. Bei mir funktioniert die Formel von Harry einwandfrei.
Hier noch eine andere Variante.
Tabelle1

 ABC
10,2  
20,1  
30,05  
4   
5   
6   
7   
8   
9   
10   
11   
12303 35,15

Formeln der Tabelle
ZelleFormel
C12=MAX(A12-300;0)*A3+MAX(MIN(A12;300)-50;0)*A2+MIN(A12;50)*A1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruss Sepp

Anzeige
AW: es funzt !!!
30.01.2016 19:35:44
Bernd
Hallo Sepp,
Habe es nun mit Deiner Formel versucht und siehe da, es funktioniert einwandfrei. Ich glaube auch, dass alle anderen Vorschläge zum Ziel geführt hätten, aber dafür war ich vielleicht zu dämlich :-)
Auf alle Fälle Dir und den anderen ein dickes Dankeschön und schönes WE noch, wirklich tolles Forum und SUPER USER, die einem weiter helfen.
mfg Bernd

AW: aber wohl nur bis 640 km oder? ...
31.01.2016 09:56:25
...
Hallo Bernd,
... gilt nun die max Obergrenze von 52,00 als Ergebniswert nicht mehr?
Eingangs hattest Du eine Grenze von 250 km angegeben ab der 0,1 nicht mehr und dafür 0,005 galt. Die Grenze hattest Du dann später auf 300 km geändert und nebenbei die 0,005 auf 0,05 geändert. Und nun ist "nebenher" auch die max Obergrenze noch weggefallen?
Gruß Werner
.. , - ...

@ Sepp !!!!
31.01.2016 17:32:12
Bernd
Hallo Sepp,
wo Du es nun erwähnst, stimmt, Du hast recht. Es ist nun nicht mehr mit 52,00 Euro begrenz. Es war da wohl meine Anfangseuphorie zu groß.
Und 0,005 war leider ein Tippfehler, hättest Du aber meine Beispieldatei angesehen, wäre es auch klar gewesen, dass ich 0,05 meinte - Irren ist nun mal menschlich!
Auf irgendeine Begrenzung mit 250km kann ich mich nicht Erinnern, wo steht das? Das liest anscheinend nur Du so raus.
Fakt ist aber auch, dass Deine Lösung ein falsches Ergebnis liefert, egal mit welchen Werten.
Und ich Frage mich nun, ist das hier ein Hilfe-oder ein Diskussionsforum? Denn verzeihe mir, aber Du bist ja nur am herumjammern, anstatt ein vernünftige Lösung zu präsentieren. Wenn ein Fragensteller ein paar Fehler macht, wird dies wohl damit zu begründen sein, dass nicht jeder so ein Genie, wie Du es bist Sepp, sein wird.
Überdies habe ich mich herzlich bei jedem bedankt und bin nicht so scharf darauf, mit einem Sinnlosen Beitrag in der Positionsliste weiter nach oben zu gelangen.
Und abschließend sei gesagt, dass ich letztendlich erst morgen in meiner Arbeit die Lösung auf Herz und Nieren getestet hätte. Da wäre ich dann vermutlich auch selbst draufgekommen, dass die Begrenzung mit 52 fehlt.
Entschuldigung das ich mich am WE nicht so sehr für meine dienstlichen Sachen interessiere.
mfg Bernd

AW: ... Du meinst offensichtlich mich ...
31.01.2016 18:06:07
...
Hallo Bernd,
... denn zuletzt hatte ich Dir geschrieben, auf was Du jetzt "geantwortet" hast nur Dich dabei im Ton etwas vergriffen hast. Vielleicht liegt es ja nur am Wetter.
Nicht immer konnte man Deine hier im thread getroffenen Aussagen so eindeutig als falsch erkennen, wie schon bei Deiner hiesigen Betreffaussage: "@Sepp".
Sieh Dir Morgen nochmal bei Tageslicht und in Ruhe Deine Ausgangsfragestellung und später getroffenen Angaben an und die Feststellungen, die sowohl harry, Sepp und ich hierzu getroffen haben. Es waren und sind nicht nur einmalige Tippfehler, die uns teilweise irritiert hatten und haben.
Gruß Werner
.. , - ...

Staffelpreise
31.01.2016 19:00:27
Josef
Hallo Bernd
Wie Werner schon schreibt, gehe auch ich davon aus, dass dieser Post nicht für mich bestimmt ist.
Die Max.bedingung von 52 habe ich in deiner Beschreibung glatt übersehen.
Hier nun die korrigierte Variante:
=MIN(MAX(A12-300;0)*A3+MAX(MIN(A12;300)-50;0)*A2+MIN(A12;50)*A1;52)
Bei dieser Formel ist aber das Ergebnis nicht null, wenn in A12 nichts steht.
Hier deshalb noch eine kürzere Variante, die zusätzlich diesen Fehler behebt.
=MIN(A12*A1-MAX(0;A12-300)*(A2-A3)-MAX(0;A12-50)*(A1-A2);52)
Gruss Sepp

AW: Staffelpreise
31.01.2016 21:44:15
Bernd
Hallo Sepp,
hoffentlich der richtige nun :-)
Natürlich war der Post nicht für Dich sondern an Werner gerichtet. Habe nicht nur wirres Zeugs in meine Post geschrieben, habe auch die Namen verwechselt. Ja, wenn mal was schief läuft, dann richtig.
Tausend Dank für Deine Hilfe. Gibt ja andere hier, die lieber nur herum weinen anstatt zu helfen.
Jetzt funktioniert es exakt wie ich wollte.
Wünsche noch einen schönen restlichen Sonntag,
mfg Bernd

Andere, die 'herumweinen'...
01.02.2016 04:04:06
Luc:-?
Na, da kann ich ja froh sein, Bernd,
dass ich mich an der Lösung deines Problems nicht beteiligt hatte (oder viell auch eher du!)… :-|
Werner ist nunmal ein Perfektionist, der gern Details nachfragt, um dann auch eine brauchbare Lösung zu liefern. Dabei kümmert er sich, wie übrigens andere auch, auch am WE um deine „dienstlichen Belange“! Von jemand, der hier eine Frage stellt, wird erwartet, dass er eigenen Einsatz zeigt, ggf auch am WE, und sein Problem geordnet darstellt und nicht „wirres Zeug“ schreibt!
Nebenbei, hier hat niemand Grund, solchen Chaoten auch nur eine Träne „nachzuweinen“ und du erwirbst mit dem Stellen einer Frage hier auch kein Recht auf eine Antwort. Das ist nicht mal auf Bezahl-Seiten so, wenn kein Vertrag zustande kommt.
Insofern war deine AW an Werner nicht nur wirr, sondern tatsächlich ein Vergreifen im Ton. Das sollte dir klar sein, denn davon könnte zukünftige AW-Bereitschaft abhängen…
Beachte, neben vielen gelegentlichen und „Nachwuchshelfern“ sind hier auch einige stark engagierte Cracks auf den diversen XlTeilgebieten zugange. Mit neopa hast du einen davon ohne Not verärgert. So etwas wird hier durchaus zK genommen…
Gruß, Luc :-?

AW: Sei, wie es sei...
01.02.2016 07:13:33
Bernd
Lieber Helfer und Beitragsverfasser,
ich Stimme inhaltlich voll den Vorpostern zu und entschuldige mich für meine nicht gerade korrekt gewählte Tonart.
Ich sage nochmals Tausend Dank und bin froh, dass mir geholfen werden konnte.
Das nächste mal werde ich zuerst Denken dann schreiben, war wohl ein schlechtes WE für mich, SORRY.
mfg Bernd

AW: nun denn, Schwamm drüber ...
01.02.2016 14:43:14
...
Hallo Bernd,
... so ein schlechtes WE kann einen schon mal die Stimmung vermiesen.
@Luc,
... danke für die Blumen, verärgert war ich hier diesmal nicht wirklich, eher im ersten Moment etwas verdutzt.
Einen schönen Abend dann noch.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige