Generisk formel
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Sammendrag
Hvis du trenger å avrunde et tall til et gitt (variabelt) antall angitte sifre eller figurer, kan du gjøre det med en elegant formel som bruker funksjonene RUND og LOG10.
I eksemplet vist er formelen i D6 som følger:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Forklaring
Dette kan være en skremmende formel hvis du ikke har god bakgrunn i matematikk, men la oss jobbe gjennom det trinn for trinn.
For det første, når du har en formel som denne der en funksjon (i dette tilfellet RUNDE) brytes rundt alle andre, er det ofte nyttig å jobbe utenfra. Så i utgangspunktet avrunder denne formelen verdien i B6 ved hjelp av RUND funksjon:
=ROUND(B6,x)
Hvor x er antall nødvendige sifre. Den vanskelige delen av denne formelen er å beregne x. Dette er en variabel, fordi den vil endres avhengig av antallet som avrundes. x beregnes med denne biten:
C6-(1+INT(LOG10(ABS(B6))))
Dette virker komplisert, så la oss først se på hvordan formelen må fungere for de gitte eksemplene. Husk med RUND at et negativt antall sifre fungerer på venstre side av desimaltallet. Så for å avrunde 1234567 til et økende antall signifikante sifre, ville vi ha:
=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å hovedproblemet er hvordan man beregner -6, -5, -4 og så videre, avhengig av antallet vi avrunder.
Nøkkelen er å forstå hvordan disse tallene kan uttrykkes ved hjelp av eksponenter, som i vitenskapelig notasjon:
=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
Merk at eksponenten er 6 i alle tilfeller, som bestemmes med denne biten:
INT(LOG10(ABS(B6)))
Så resten av formelen bruker bare den beregnede eksponentverdien for å finne ut riktig tall for å gi RUNDE avhengig av antall ønskede sifre:
=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å oppsummert:
- ABS konverterer verdien til en absolutt (positiv) verdi
- LOG10 får eksponenten, i dette tilfellet 6 med en desimalverdi
- INT trimmer av desimaldelen av eksponenten
- Formelen bruker eksponenten og de medfølgende signifikante sifrene for å finne ut riktig antall sifre for å gi RUND
- RUND avrunder tallet ved å bruke antall sifre som er oppgitt
