Rounding options in a formula

[Caly]

Hi I have multiple dollar value ranges and am trying to apply one formula for each option but is there a better way to write the formula below?

=if(and(A2>10,000,A2<100,000),roundup(A2,-4),
if(and(A2>100,000,A2<1,000,000),roundup(A2,-5),
if(and(A2>1,000,000,A2<1,000,000,000),roundup(A2,-6),
Roundup(A2,-7))))

Column A - Dollars
$12,036,219.22
$437,379.70
$11,737.18
$180,595.28

 

[Caly]

Thank you again this is great and works in many projects. Question on the formula, what is the “-MIN” and how does the “LOG10” work? With a starting number of $11,737.18, the value becomes $20,000.00 using the formula =Roundup($11,737.18”,-MIN(6,(LOG10($11,737.18))))

Is the roundup raising the starting number and the “-MIN(6,” looking to see if the starting number has 6 digits?
Is the “LOG10” then taking that starting value and raising it to next value by 10?

 

[offthelip]

With a starting number of $11,737.18, the value becomes $20,000.00 using the formula =Roundup($11,737.18”,-MIN(6,(LOG10($11,737.18))))

this is exactly what your original equation did.

=IF(AND(A2>10000,A2<100000),ROUNDUP(A2,-4))

does the same.
To explain how it works. the log10 function is way of expressing any number as power of 10, So 100 is 10 times 10 which is expressed as 10 to power 2 , 1000 is 10 * 10 * 10 which is 10 to power of 3
So if you take the LOG10 of the 11737.18 it come back with 4.069557 . So if a number is between 100 and 1000 then the log10 of it will be between 2 and 3 . similarly if the number is between 1000 and 10000 the log10 of it will be between 3 and 4. and so on , i.e. the log10 tells me how many numbers there are to the left of the decimal point. You requirement to roundup followed this almost exactly. Since round up need an integer input ( the number decimal places) I used the INT function to change 4.069557 into integer 4. ( ie. it rounds down) . This works perfectly up to the point where you get 10 to the power of 7 , where you stated you rather it rounded to only 6 digits. This is where I introduced the MIN function which just takes the MINIMUM value of all the numbers given to it.
So when the input value is
12,036,219.22

the LOG10 of this is 7.08049 which would round it up to 20000000, which you didn't want, the MIN function takes the minimum of 6 and 7 and so it only rounds it up to 6 digits i.e 13,000,000.00

 

[Caly]

Thank you so much that makes sense. I was struggling with how to explain it and this helps tremendously. Thank you.