Formel die die Quersumme aus einer Zahl mit Nachkommastelle bildet und die Stelle hinter dem Komma automatisch aufrundet, wenn nicht durch 3 teilbar ?
Ich benötige eine Formel, die die Quersumme aus einer Zahl mit Nachkommastelle bildet und die Stelle hinter dem Komma automatisch aufrundet, wenn das Ergebnis nicht durch 3 teilbar ist.
Beispiel: 54,2 Quersumme = 11 ist nicht durch 3 teilbar
Lösung: 54,3 Quersumme= 12 ist durch 3 teilbar
Es soll immer die Stelle hinter dem Komma aufgerundet werden.
Hat jemand eine Lösung?
4 Antworten
Nun du hast den Elefanten noch nicht klein genug gehackt um ihn Stück für Stück auf die Gabel zu kriegen.
Problem 1: die Quersumme ermitteln. Ist nicht trivial wenn die Anzahl an Vorkomma oder Nachkommazahlen variabel sein soll, denn der Wert wird intern als 80 Bit Fließkommazahl dargestellt und muss entweder in Text gewandelt werden oder rechnerisch alle Stellen abgeklappert werden.
Ein Teil der Problematik findet sich hier. Alle gegebenen Lösungen erschlagen nicht das Stellenproblem.
https://www.bing.com/search?q=quersumme+in+excel
Das würde übrigens auch mit einer VBA Lösung kaum besser. Also gefordert wäre daher: lassen sich die Stellen (Vor- und Nachkomma) irgendwie begrenzen? Falls ja, hilft jede der oben gegebene Lösung.
Problem 2: ob eine Zahl durch 3 teilbar ist, findet man mit =Rest(zahl;3) und dem Vergleich, ob das Ergebnis 0 ist.
Problem 3: Aufrunden mit der Funktion Runden(). Falls Runden an einer Nachkommastelle, dann etwa runden(zahl; -1)
Zum Problem der variabeln Anzahl an (Nachkomma)Stellen:
Als Workaround die Zahl mit einem großen 10^n (wobei n mindestens so hoch sein soll, wie Nachkommastellen möglich sind) Faktor multiplizieren. Die Quersumme von 54,12 ist die gleiche wie von 541,2 oder 5412000 oder 54120000000000.
meine Nächtliche ist leider verschluckt worden, also Nochnversuch:
54,2*10, im Folgenden ganze Zahl genannt, hat die gleiche Quersumme. DeeDee hat das in einem Kommentar zur AW von MrProcess sogar verallgemeinert, leider wäre eine Formel dazu viel zu kompliziert, um in Folgeformeln eingebettet zu werden. Muss manuell bleiben. Die allgemeine Formel für Quersumme ist eine Matrix-Formel,
Meine Linkempfehlung zu Matrix-Formeln (in 7 Stationen, Quersumme in St.7):
http://www.online - excel.de/excel/singsel.php?f=26
Das Aufrunden ginge so: Nenne die Zelle mit der ganzen Zahl GZ und die mit der Quersumme QS. (trage zunächst mal diese Werte manuell ein, Formeln später):
=GZ-REST(QS;3)+WENN(REST(QS;3)=0;0;3) (ginge vllt auch kürzer, fällt mir aber nichts ein)
Im Fall begrenzter Stellenzahlen (54,2*10=542 als Zahl) kann man die QSum auch ohne Matrixformel bilden (hier bis zu 9999):
=rechts(GZ;1)
+wenn(GZ>=10;links(rechts(GZ;2);1);0)
+wenn(GZ>=10^2;links(rechts(GZ;3);1);0)
+wenn(GZ>=10^3;links(rechts(GZ;4);1);0)
beliebig erweiterbar, xl verarbeitet ja bis zu 15 Stellen. (Sehr große Zahlen, ab 10 Trilliarden) müsste man analog den Kommazahlen auf eine max 15-stellige Ganzzahl, Trilliarden, zuschneiden). Diese Formel in QS einkopieren
Wenn noch Fragen, dann nur zu!
auch wenn ich Subopts AW für besser halte, hier doch noch eine Korrektur: Wechseln liefert einen Text, ebenso links, rechts und Teil. xl stuft jeden Text als größer ein als die größte in xl verarbeitbare Zahl. Deshalb ist GZ>10^n immer WAHR und es muss aus dem Text in GZ ein Wert gemacht werden mit *1 oder +0. Formel also:
=rechts(GZ*1;1)
+wenn(GZ*1>=10;links(rechts(GZ;2);1);0)+wenn(GZ*1>=10^2;links(rechts(GZ;3);1);0)
+wenn(GZ*1>=10^3;links(rechts(GZ;4);1);0)
Ist es mit der einen Nachkommastelle fix oder können es auch mehrere sein?
Das Ermitteln der Quersumme würde (zumindest bei meinen Versuchen) so funktionieren, also auch bei unterschiedlicher Anzahl von Nachkommastellen:
=SUMMENPRODUKT(TEIL(A1*10^(LÄNGE(REST(A1;1))-2);ZEILE(INDIREKT("1:"&LÄNGE(A1*10^(LÄNGE(REST(A1;1))-2))));1)*1)
Idee von DeeDee07 mit http://www.excelformeln.de/formeln.html?welcher=112 kombiniert
Tricky wird es aber, wenn jetzt wirklich nur die letzte Stelle der Zahl um 1 bzw. 2 erhöht werden muss.
Ich habe mal experimentiert. Vielleicht hilft es:
A1 54,2
B1 =WECHSELN(A1;",";"")
C1 =SUMMENPRODUKT(TEIL(B1;ZEILE(INDIREKT("$1:$"&LÄNGE(B1)));1)+0)
D1 =WENN(REST(C1;3)=0;AUFRUNDEN(A1;0);A1)
A2 54,3
...
Den Rest herunterziehen.
Evtl. noch Tausendertrennzeichen in B1 filtern.
Genial, das mit Wechseln und Summenprodukt so kurz hinzukriegen (und darzustellen), DH!
nur ein kleiner Schönheitsfehler: Die Formel in D1 musste ich abändern zu:
=WENN(REST(C1;3)=0;A1;(B1+3-REST(C1;3))/(B1/A1))
Ja, ich hatte die Aufgabe falsch verstanden und als ich es gemerkt habe, war ich zu faul und habe erstmal auf ein Feedback gewartet.
Danke für die Korrektur!
Besten Dank, es funktioniert!!!
Vielen Dank an alle, für eure Hilfe. Ihr habt mir wirklich sehr geholfen!!
Na, dann vergib doch einen Stern, das ist das einzige, womit ein (ggf. noch-nicht-)-Experte hier was anfangen kann! (HA sind essentiell für den Expertenstatus)
Jepp, Sterne braucht jeder (manchmal auch Experten ;) ), besonders aber bei Themen, die nicht so häufig getaggt werden, würde man sich über einen Stern sehr freuen.
DH für Erklärungund Link!
Die Sache mit dem Aufrunden habe ich aber so verstanden, dass nicht die Quersumme, sondern die zugrunde liegende Zahl aufgerundet werden soll, wenn nicht durch 3 teilbar.