Formula generica
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Sommario
Se è necessario arrotondare un numero a un determinato numero (variabile) di cifre o cifre specificate, è possibile farlo con un'elegante formula che utilizza le funzioni ROUND e LOG10.
Nell'esempio mostrato, la formula in D6 è la seguente:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Spiegazione
Questa può essere una formula intimidatoria se non hai un buon background in matematica, ma lavoriamola passo dopo passo.
Innanzitutto, quando hai una formula come questa in cui una funzione (in questo caso ROUND) avvolge tutte le altre, è spesso utile lavorare dall'esterno verso l'interno. Quindi, al centro, questa formula sta arrotondando il valore in B6 usando ROUND funzione:
=ROUND(B6,x)
Dove x è il numero di cifre significative richieste. La parte difficile di questa formula è calcolare x. Questa è una variabile, perché cambierà a seconda del numero da arrotondare. x viene calcolato con questo bit:
C6-(1+INT(LOG10(ABS(B6))))
Sembra complicato, quindi vediamo prima come deve funzionare la formula per gli esempi forniti. Ricorda con ROUND che un numero negativo di cifre funziona a sinistra del decimale. Quindi, per arrotondare 1234567 a un numero crescente di cifre significative, avremmo:
=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
Quindi, il problema principale è come calcolare -6, -5, -4 e così via a seconda del numero che stiamo arrotondando.
La chiave è capire come questi numeri potrebbero essere espressi usando esponenti, come nella notazione scientifica:
=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
Nota che l'esponente è 6 in tutti i casi, che è determinato con questo bit:
INT(LOG10(ABS(B6)))
Quindi, il resto della formula utilizza solo il valore dell'esponente calcolato per capire il numero giusto da dare ROUND a seconda del numero di cifre significative desiderate:
=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
Quindi, in sintesi:
- L'ABS converte il valore in un valore assoluto (positivo)
- LOG10 ottiene l'esponente, in questo caso 6 con un valore decimale
- INT elimina la parte decimale dell'esponente
- La formula utilizza l'esponente e le cifre significative fornite per calcolare il numero corretto di cifre per ottenere ROUND
- ARROTONDO arrotonda il numero utilizzando il numero di cifre fornite
