Runden im Wissenschaftsformat

[Peter Hewing]

Hallo Excel-Experten,

ich habe in Tabellen Zahlen mit 15 Nachkommastellen. Dabei sind auch sehr
kleine Zahlen, z. B. 0,000000084567835 oder 0,000345678901112.

Zum Anzeigen habe ich das Wissenschaftsformat gewählt.

Ich möchte diese Zahlen nun runden, so dass im Wissenschaftsformat nur 4
Nachkommastellen vorhanden sind.

So sollte aus der ersten Zahl 8,4568000E-08 und aus der zweiten Zahl
3,4568000E-04 werden. Geht das mit den Funktionen? Ich habe die Hilfe und
google bereits bemüht, bin aber nicht fündig geworden.

Grüße

Peter

[b***@gmail.com]

Hallo Peter,

zum Beispiel: =--TEXT(A1;"#,####E+###")

Oder als UDF:

Function dbl2nsig(d As Double, _
Optional n As Long = 5) _
As String
'Returns string presentation of d with
'n significant digits. PB V0.01
Dim i As Long, j As Long
Dim s As String, sr As String
s = Format(d, "0." & String(n - 1, "0") _
& "E+000")
i = Right(s, 4)
Select Case i
Case Is > n - 2
sr = Left(s, 1)
If n > 1 Then sr = sr & Mid(s, 3, n - 1)
sr = sr & String(i - n + 1, "0")
Case 0
sr = Left(s, n + 1)
Case Is < 0
sr = "0." & String(-1 - i, "0") & Left(s, 1) _
& Mid(s, 3, n - 1)
Case Else
s = Left(s, 1) & Mid(s, 3, n - 1)
sr = Left(s, i + 1) & "." & _
Right(s, n - i - 1)
End Select
dbl2nsig = sr
End Function

HTH,
Bernd

[Peter Hewing]

Post by b***@gmail.com
Hallo Peter,
zum Beispiel: =--TEXT(A1;"#,####E+###")

Das funktioniert nur bedingt, weil ich damit nicht mehr rechnen. Wenn ich
dann den Text wieder in einen Wert umwandel, habe ich wieder alle 15
Stellen.

Post by b***@gmail.com
Function dbl2nsig(d As Double, _
Optional n As Long = 5) _
As String
'Returns string presentation of d with
'n significant digits. PB V0.01
Dim i As Long, j As Long
Dim s As String, sr As String
s = Format(d, "0." & String(n - 1, "0") _
& "E+000")
i = Right(s, 4)
Select Case i
Case Is > n - 2
sr = Left(s, 1)
If n > 1 Then sr = sr & Mid(s, 3, n - 1)
sr = sr & String(i - n + 1, "0")
Case 0
sr = Left(s, n + 1)
Case Is < 0
sr = "0." & String(-1 - i, "0") & Left(s, 1) _
& Mid(s, 3, n - 1)
Case Else
s = Left(s, 1) & Mid(s, 3, n - 1)
sr = Left(s, i + 1) & "." & _
Right(s, n - i - 1)
End Select
dbl2nsig = sr
End Function

Wenn die UDF auch Text erzeugt bringt mir das nichts. Vielleicht noch mal
etwas mehr Hintergrund. Die Zahlen sind Anteile eines Ganzen und ergeben in
der Summe 100 %. Nach dem Runden auf die 4. Dezimalstelle im
Wissenschaftsformat sollte das wieder 100 % ergeben und ich muss mit den
Zahlen weiter rechnen können.

Post by b***@gmail.com
HTH,
Bernd

Vielen Dank.

Grüße

Peter

[b***@gmail.com]

Hallo Peter,

Gib doch bitte einmal Deine Beispieldaten ein und ueberzeuge Dich, dass
Deine gewuenschten Beispielergebnisse herauskommen. Als Zahlen versteht
sich, nicht als Text.

Deine Einfuehrung der Nebenbedingung, dass die Gesamtsumme der Zahlen
sich nicht aendern soll, erhoeht die Komplexitaet der Aufgabe
allerdings betraechtlich. Bitte gib an, nach welcher Regel welche
Deiner Zahlen zur "falschen" Seite gerundet werden sollen, damit die
Gesamtsumme identisch bleibt.

Beispielzahlen wuerden helfen.

Viele Gruesse,
Bernd

[b***@gmail.com]

Es ist z. B.
8,4568000E-08 = --TEXT(0,000000084567835;"#,####E+###")
oder
8,4568000E-08 = --dbl2nsig(0,000000084567835;5) =
N(dbl2nsig(0,000000084567835;5))

Gruss,
Bernd

[Peter Hewing]

Post by b***@gmail.com
Gib doch bitte einmal Deine Beispieldaten ein und ueberzeuge Dich, dass
Deine gewuenschten Beispielergebnisse herauskommen. Als Zahlen versteht
sich, nicht als Text.

Ich habe es an meinen tatsächlichen Zahlen ausprobiert. Als ich damit wieder
die Summe bilden wurde, hat Excel "0" ausgegeben.

Hier mal acht tatsächliche Zahlen:
0,440730878707189
0,543568083738867
0,00356257460288311
0,00550913598383987
0,000282802313837113
0,00587641171609586
0,000312184372417592
0,000157928564870076

In der Summe ergibt das 1,000...

Post by b***@gmail.com
Deine Einfuehrung der Nebenbedingung, dass die Gesamtsumme der Zahlen
sich nicht aendern soll, erhoeht die Komplexitaet der Aufgabe
allerdings betraechtlich. Bitte gib an, nach welcher Regel welche
Deiner Zahlen zur "falschen" Seite gerundet werden sollen, damit die
Gesamtsumme identisch bleibt.

Eine Regel zu definieren wird schwierig. Am einfachsten wird es sein nach
den üblichen Regeln auf- oder abzrunden und die Differenz zu 1 der kleinsten
Zahl zu zuschlagen.

Post by b***@gmail.com
Beispielzahlen wuerden helfen.

s.o.

Post by b***@gmail.com
Viele Gruesse,
Bernd

Grüße

Peter

[b***@gmail.com]

Hallo Peter,

hier zunächst eine Korrektur meiner UDF für deutsche Excel Anwender:
Option Explicit
Const Cs_decpoint = "," '"," in Germany, "." in UK
Function dbl2nsig(d As Double, _
Optional n As Long = 5) _
As String
'Returns string presentation of d with
'n significant digits. PB V0.02
Dim i As Long, j As Long
Dim s As String, sr As String
s = Format(d, "0." & String(n - 1, "0") _
& "E+000")
i = Right(s, 4)
Select Case i
Case Is > n - 2
sr = Left(s, 1)
If n > 1 Then sr = sr & Mid(s, 3, n - 1)
sr = sr & String(i - n + 1, "0")
Case 0
sr = Left(s, n + 1)
Case Is < 0
sr = "0." & String(-1 - i, "0") & Left(s, 1) _
& Mid(s, 3, n - 1)
Case Else
s = Left(s, 1) & Mid(s, 3, n - 1)
sr = Left(s, i + 1) & "." & _
Right(s, n - i - 1)
End Select
dbl2nsig = Replace(sr, ".", Cs_decpoint)
End Function

Nun zu Deiner Aufgabe: Die Komplexität übersteigt leider derzeit die
Kapazität meiner Kaffeepausen. Ein Lösungsansatz: Ich würde eine
Prozedur (Makro: sub) schreiben, die
1. Die Originalzahlen mit meiner UDF dbl2nsig rundet.
2. Die Rundungszahlen "zur falschen" Seite und dann die Differenzen zu
den Rundungen unter 1. ermittelt.
3. Mit Michael Schwimmers UDF unter
http://michael-schwimmer.de/vba096.htm guckt, ob die Differenz der
Summe der Zahlen unter 1. und der Originalsumme mit den Differenzen
unter 2. dargestellt werden kann.
4. Falls die Darstellung möglich ist, die Lösung - wobei "falsch"
gerundete Zahlen vielleicht eine andere Hintergrundfarbe erhalten
sollten - ausgibt.

HTH,
Bernd

[Klaus "Perry" Pago]

Post by Peter Hewing
0,440730878707189
0,543568083738867
0,00356257460288311
0,00550913598383987
0,000282802313837113
0,00587641171609586
0,000312184372417592
0,000157928564870076
In der Summe ergibt das 1,000...

Post by b***@gmail.com
Deine Einfuehrung der Nebenbedingung, dass die Gesamtsumme der Zahlen
sich nicht aendern soll, erhoeht die Komplexitaet der Aufgabe
allerdings betraechtlich. Bitte gib an, nach welcher Regel welche
Deiner Zahlen zur "falschen" Seite gerundet werden sollen, damit die
Gesamtsumme identisch bleibt.

Eine Regel zu definieren wird schwierig. Am einfachsten wird es sein nach
den üblichen Regeln auf- oder abzrunden und die Differenz zu 1 der
kleinsten Zahl zu zuschlagen.

Hallo Peter,

deine Zahlen stehen in A1 bis A8 - dann müsste folgende Matrixformel das
Problem lösen:

B1:
=WENN(A1=MIN($A$1:$A$8);RUNDEN(A1;4-GANZZAHL(LOG10(A1)))-SUMME(RUNDEN($A$1:$A$8;4-GANZZAHL(LOG10($A$1:$A$8))))+1;RUNDEN(A1;4-GANZZAHL(LOG10(A1))))
als Matrixformel mit STRG+SHIFT+RETURN abschliessen und bis B8
herunterkopieren.

Die Differenz der Rundungssumme zu 1 wird mit der Rundung der kleinsten Zahl
ausgeglichen. Für andere Zellbereiche kann die Formel leicht angepasst
werden.

Gruß
Klaus

[Klaus "Perry" Pago]

Post by Peter Hewing
Hallo Excel-Experten,
ich habe in Tabellen Zahlen mit 15 Nachkommastellen. Dabei sind auch sehr
kleine Zahlen, z. B. 0,000000084567835 oder 0,000345678901112.
Zum Anzeigen habe ich das Wissenschaftsformat gewählt.
Ich möchte diese Zahlen nun runden, so dass im Wissenschaftsformat nur 4
Nachkommastellen vorhanden sind.
So sollte aus der ersten Zahl 8,4568000E-08 und aus der zweiten Zahl
3,4568000E-04 werden. Geht das mit den Funktionen? Ich habe die Hilfe und
google bereits bemüht, bin aber nicht fündig geworden.

Hallo Peter,

wie wäre es mit
=RUNDEN(A1;3-GANZZAHL(LOG10(A1)))

Gruß
Klaus

[Klaus "Perry" Pago]

Post by Klaus "Perry" Pago
=RUNDEN(A1;3-GANZZAHL(LOG10(A1)))

man sollte schon richtig lesen - die Formel liefert nur 3 Nachkommastellen -
also besser:
=RUNDEN(A1;4-GANZZAHL(LOG10(A1)))

Klaus