Generisk formel
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Resumé
Hvis du har brug for at afrunde et tal til et givet (variabelt) antal specificerede cifre eller tal, kan du gøre det med en elegant formel, der bruger funktionerne RUND og LOG10.
I det viste eksempel er formlen i D6 som følger:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Forklaring
Dette kan være en skræmmende formel, hvis du ikke har en god baggrund i matematik, men lad os arbejde igennem det trin for trin.
For det første, når du har en formel som denne, hvor en funktion (i dette tilfælde ROUND) ombrydes omkring alle andre, er det ofte nyttigt at arbejde udefra. Så kernen afrunder denne formel værdien i B6 ved hjælp af ROUND fungere:
=ROUND(B6,x)
Hvor x er antallet af signifikante cifre, der kræves. Den vanskelige del af denne formel er at beregne x. Dette er en variabel, fordi den vil ændre sig afhængigt af antallet, der afrundes. x beregnes med denne bit:
C6-(1+INT(LOG10(ABS(B6))))
Dette virker kompliceret, så lad os først se på, hvordan formlen skal fungere for de givne eksempler. Husk med RUND, at et negativt antal cifre virker på venstre side af decimalen. Så for at afrunde 1234567 til et stigende antal signifikante cifre, ville vi have:
=ROUND(1234567,-6) = 1000000 // 1 sig. digit =ROUND(1234567,-5) = 1200000 // 2 sig. digits =ROUND(1234567,-4) = 1230000 // 3 sig. digits =ROUND(1234567,-3) = 1235000 // 4 sig. digits
Så det største problem er, hvordan man beregner -6, -5, -4 og så videre afhængigt af antallet, vi afrunder.
Nøglen er at forstå, hvordan disse tal kunne udtrykkes ved hjælp af eksponenter, som i videnskabelig notation:
=ROUND(1234567,-6) = 1000000 = 1.0*10^6 =ROUND(1234567,-5) = 1200000 = 1.2*10^6 =ROUND(1234567,-4) = 1230000 = 1.23*10^6 =ROUND(1234567,-3) = 1235000 = 1.235*10^6
Bemærk, at eksponenten i alle tilfælde er 6, hvilket bestemmes med denne bit:
INT(LOG10(ABS(B6)))
Så resten af formlen bruger bare den beregnede eksponentværdi til at finde ud af det rigtige tal, der giver RUND, afhængigt af antallet af ønskede signifikante cifre:
=ROUND(1234567,-6) // 1-(1+6) = -6 =ROUND(1234567,-5) // 2-(1+6) = -5 =ROUND(1234567,-4) // 3-(1+6) = -4 =ROUND(1234567,-3) // 4-(1+6) = -3
Så sammenfattende:
- ABS konverterer værdien til en absolut (positiv) værdi
- LOG10 får eksponenten, i dette tilfælde 6 med en decimalværdi
- INT trimmer decimaldelen af eksponenten af
- Formlen bruger eksponenten og de medfølgende signifikante cifre til at finde ud af det rigtige antal cifre til at give RUND
- RUND afrunder nummeret ved hjælp af antallet af leverede cifre
