Formel die die Quersumme aus einer Zahl mit Nachkommastelle bildet und die Stelle hinter dem Komma automatisch aufrundet, wenn nicht durch 3 teilbar ?

...komplette Frage anzeigen

4 Antworten

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!

Iamiam 06.01.2016, 15:06

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)
0

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)

Iamiam 06.01.2016, 02:29

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.

1
Iamiam 06.01.2016, 02:34
@Iamiam

stelle grad fest, dass meine AW leider verschluckt worden ist, und jetzt um halb drei nachts leg ich mich erst mal aufs Ohr!

1
DeeDee07 06.01.2016, 06:37

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.

3

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.

Antman1000 06.01.2016, 12:56

Vielen Dank, werde es heute Abend mal ausprobieren.

0
Iamiam 06.01.2016, 13:59
@Antman1000

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))

1
Suboptimierer 06.01.2016, 14:00
@Iamiam

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!

1
Antman1000 06.01.2016, 19:48
@Iamiam

Besten Dank, es funktioniert!!!

Vielen Dank an alle, für eure Hilfe. Ihr habt mir wirklich sehr geholfen!!

1
Iamiam 13.01.2016, 13:27
@Suboptimierer

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)

1
Suboptimierer 13.01.2016, 13:31
@Iamiam

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.

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.

Was möchtest Du wissen?