Fórmula genérica
=ROUND(number,digits-(1+INT(LOG10(ABS(number)))))
Resumo
Se você precisar arredondar um número para um determinado número (variável) de dígitos ou números especificados, pode fazê-lo com uma fórmula elegante que usa as funções ROUND e LOG10.
No exemplo mostrado, a fórmula em D6 é a seguinte:
=ROUND(B6,C6-(1+INT(LOG10(ABS(B6)))))
Explicação
Esta pode ser uma fórmula intimidante se você não tiver um bom conhecimento em matemática, mas vamos trabalhar nisso passo a passo.
Primeiro, quando você tem uma fórmula como esta, em que uma função (neste caso ROUND) envolve todas as outras, geralmente é útil trabalhar de fora para dentro. função:
=ROUND(B6,x)
Onde x é o número de dígitos significativos necessários. A parte complicada desta fórmula é calcular x. Esta é uma variável, porque mudará dependendo do número que está sendo arredondado. x é calculado com este bit:
C6-(1+INT(LOG10(ABS(B6))))
Isso parece complicado, então vamos primeiro ver como a fórmula precisa funcionar para os exemplos fornecidos. Lembre-se de que com ROUND, um número negativo de dígitos funciona no lado esquerdo do decimal. Portanto, para arredondar 1234567 para um número crescente de dígitos significativos, teríamos:
=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
Portanto, o principal problema é como calcular -6, -5, -4 e assim por diante, dependendo do número que estamos arredondando.
A chave é entender como esses números podem ser expressos usando expoentes, como na notação científica:
=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
Observe que o expoente é 6 em todos os casos, que é determinado com este bit:
INT(LOG10(ABS(B6)))
Portanto, o resto da fórmula é apenas usar o valor do expoente calculado para descobrir o número certo para dar ROUND dependendo do número de dígitos significativos desejados:
=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
Então, em resumo:
- ABS converte o valor em um valor absoluto (positivo)
- LOG10 obtém o expoente, neste caso 6 com um valor decimal
- INT remove a parte decimal do expoente
- A fórmula usa o expoente e os dígitos significativos fornecidos para descobrir o número certo de dígitos para dar ROUND
- ROUND arredonda o número usando o número de dígitos fornecidos
