Is it possible to round to different decimal places in excel based on the range?-CodePudding

I want to set up an excel sheet that would round the value to different decimal places based on the range of the value entered. I tried using different formulas but had no luck. The image shows the requirement. Is it possible to achieve it using nested IF? or would I need VB script or similar? Would someone be able to give me an example of a iIF formula I can use at least for two ranges, then I can set it up for the remaining ones.

CodePudding user response：

Here's a couple of simple formulas using a lookup table:

The LookupTable is just the numbers from the original table.

The ResultsTable has these formulas:

Nearest: =VLOOKUP([@Sample],LookupTable,2,TRUE)

Result: =[@Nearest]*ROUND([@Sample]/[@Nearest],0)

You can even do this as a single formula, like this:

=LET(Nearest,VLOOKUP(A2,{0,0.05;1,0.1;10,1;40,5;100,10;400,50;1000,100},2,TRUE),Nearest*ROUND(A2/Nearest,0))

CodePudding user response：

To get numbers to the proper rounded integer (vs decimal) as the question requests, something like this should work.

=if(A2<=1,MROUND(A2*100,5)/100,if(A2<=10,round(A2,1),if(A2<=40,mround(A2,1),if(A2<=100,MROUND(A2,5),if(A2<=400,mround(A2,10),if(A2<=1000,MROUND(A2,50),mround(A2,100)))))))

CodePudding user response：

Try This:

Assuming that the number is in cell A2:

=IF(A2<1,ROUNDUP(A2,2),IF(A2<10,ROUNDUP(A2,1),IF(A2<40,ROUNDUP(A2,0),IF(A2<100,CEILING(A2,5),IF(A2<400,CEILING(A2,10),IF(A2<1000,CEILING(A2,50),CEILING(A2,100)))))))

CodePudding user response：

I've interpreted your question based on your words, not the image. This may very well be wrong but you have another answer so one of the approaches will likely help.

You can do it via the TEXT formula.

=IF(A3<=1, TEXT(A3, "#,##0.00"),IF(A3<=1000, TEXT(A3, "#,##0"), TEXT(A3, "#,##,K")))

Above is a basic example of a few ways to format the cell.

A VLOOKUP would work as well, you just need do that part of it.