Generisk formel
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Sammanfattning
Om du behöver avrunda ett nummer till ett givet (variabelt) antal angivna siffror eller siffror kan du göra det med en elegant formel som använder funktionerna RUND och LOG10.
I exemplet som visas är formeln i D6 följande:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Förklaring
Detta kan vara en skrämmande formel om du inte har en bra bakgrund i matematik, men låt oss arbeta igenom det steg för steg.
Först, när du har en sådan formel där en funktion (i det här fallet RUND) sveper runt alla andra, är det ofta bra att arbeta utifrån. Så i grunden avrundar denna formel värdet i B6 med hjälp av RUND fungera:
=ROUND(B6,x)
Där x är antalet signifikanta siffror som krävs. Den knepiga delen av denna formel är att beräkna x. Detta är en variabel, eftersom den kommer att ändras beroende på antalet som avrundas. x beräknas med denna bit:
C6-(1+INT(LOG10(ABS(B6))))
Detta verkar komplicerat, så låt oss först titta på hur formeln måste fungera för de givna exemplen. Kom ihåg med RUND att ett negativt antal siffror fungerar på vänster sida av decimaltalet. Så för att avrunda 1234567 till ett ökande antal signifikanta siffror skulle 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å det största problemet är hur man beräknar -6, -5, -4 och så vidare beroende på antalet vi avrundar.
Nyckeln är att förstå hur dessa siffror kan uttryckas med hjälp av exponenter, som i vetenskaplig 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
Observera att exponenten i alla fall är 6, vilket bestäms med denna bit:
INT(LOG10(ABS(B6)))
Så resten av formeln använder bara det beräknade exponentvärdet för att räkna ut rätt nummer för att ge RUND beroende på antalet önskade signifikanta siffror:
=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å sammanfattat:
- ABS omvandlar värdet till ett absolut (positivt) värde
- LOG10 får exponenten, i detta fall 6 med ett decimalvärde
- INT trimmar av decimaldelen av exponenten
- Formeln använder exponenten och de medföljande signifikanta siffrorna för att räkna ut rätt antal siffror som ger RUND
- ROUND avrundar numret med det angivna antalet siffror
