Check out some similar questions!

gramon · May 23, 2007, 10:32 PM

Hi,
I am working on spreadsheets with values that I want to be able to round up based on this criteria:

If the number ends in 0,1,2, then round to nearest 0
If the number ends in 3,4,5, then round to nearest 5
If the number ends in 6,7,8,9 then round to nearest 9

The spreadsheet contains dollar values and the majority are in the thousands, so they have 4-5 digits each.

I was trying to create an IF statement that would look at the value, identify the last digit of the value as defined above and then round the number accordingly.

So if I have a value of $1,991 the formula would round the number to $1,990
or if I have a value of $1,993 the formula would round the number to $1,995
and if I have a value of $1,996 the formula would round the number to $1,999.

I am working with thousands and thousands of cells so a formula would be so helpful instead of going through the sheet. I have tried the MROUND and CEILING formulas but I can not round to the nearest "9" with those types of formulas.

Your help is greatly appreciated.

WvR · May 24, 2007, 12:02 AM

Originally Posted by gramon

Hi,
I am working on spreadsheets with values that I want to be able to round up based on this criteria:

If the number ends in 0,1,2, then round to nearest 0
If the number ends in 3,4,5,, then round to nearest 5
If the number ends in 6,7,8,9 then round to nearest 9

The spreadsheet contains dollar values and the majority are in the thousands, so they have 4-5 digits each.

I was trying to create an IF statement that would look at the value, identify the last digit of the value as defined above and then round the number accordingly.

So if I have a value of $1,991 the formula would round the number to $1,990
or if I have a value of $1,993 the formula would round the number to $1,995
and if I have a value of $1,996 the formula would round the number to $1,999.

I am working with thousands and thousands of cells so a formula would be so helpful instead of going through the sheet. I have tried the MROUND and CEILING formulas but I can not round to the nearest "9" with those types of formulas.

Your help is greatly appreciated.

you can combine the IF, REPLACE function to this

=REPLACE(D11,LEN(D11),1,IF(VALUE(RIGHT(D11))<3,0,I F(VALUE(RIGHT(D11))<6,5,IF(VALUE(RIGHT(D11))<10,9, 0))))

where D11 is the cell with the $amount

LEN(D11) ensures that you will get the right answer for any length of numbers

Hope this helps

gramon · May 25, 2007, 01:12 PM

Originally Posted by WvR

you can combine the IF, REPLACE function to this

=REPLACE(D11,LEN(D11),1,IF(VALUE(RIGHT(D11))<3,0,I F(VALUE(RIGHT(D11))<6,5,IF(VALUE(RIGHT(D11))<10,9, 0))))

where D11 is the cell with the $amount

LEN(D11) ensures that you will get the right answer for any lenght of numbers

Hope this helps

Thanks for the response. I tried the formula in my spreadsheet and this is what the formula gave me:
Original # FORMULA CALCULATIONS
2,791 2790.76923076925
2,960 2960
246 246.153846153849
246 246.153846153849
2,922 2921.53846153849
3,091 3090.76923076925
2,922 2921.53846153849
3,091 3090.76923076925
246 246.153846153849

It's round after the decimal. Not sure why. The formal was a little more "complicated" so I don't quite understand the logic, otherwise I might be able to figure out why it is doing this...
Thanks!

WvR · May 27, 2007, 11:30 PM

sorry my fault - I didn't cater for decimals, this should fix the problem

=REPLACE(ROUND(D11,0),LEN(ROUND(D11,0)),1,IF(VALUE (RIGHT(ROUND(D11,0)))<3,0,IF(VALUE(RIGHT(ROUND(D11 ,0)))<6,5,IF(VALUE(RIGHT(ROUND(D11,0)))<10,9, 0))))