round formula in excel 2

[chochoo]

hi guys
i've a question. i'm using office 2000. i've a calculated field that always contains decimal values like 30.34, 45,09 etc
i've another field in which i want to round the values deciding the values after decimals. if the value after point is between 1 to 20 e.g 12.09 then it would check that value and round it suppose 12.09 , 09 is between 1 and 20 but less then 10 so it would be 12.00 after round and if it is greater then 10 then it would be 12.20. means i want to round the value base on the gap of 20. e.g
1 to 20 ,
21 to 40,
41 to 60,
61 to 80,
81 to 99

is there any way to do this? if yes then plz respond me
thanks in advance

 

[woja]

You'll need to do this with some VBA code to create a user-defined function. I can post an outline of the code if you know VBA.

I assume what you're trying to do is this:
[ol]
[li]Given a value [tt]x[/tt], extract the part after the decimal point as [tt]d[/tt] and the part before the decimal point as [tt]i[/tt];[/li]
[li]Check the value of [tt]d[/tt]:
[ol]
[li]If it's between [tt]0[/tt] and [tt]20[/tt], inclusive, (I assume that you want to include values such as [tt]12.00[/tt] in this range), then if the value is less than [tt]10[/tt], then the value of [tt]d[/tt] becomes [tt]00[/tt], otherwise (if it's greater than or equal to [tt]10[/tt]) it becomes [tt]20[/tt];[/li]
[li]If it's between [tt]21[/tt] and [tt]40[/tt], inclusive, then if the value is less than [tt]30[/tt], then the value of [tt]d[/tt] becomes [tt]20[/tt], otherwise (if it's greater than or equal to [tt]30[/tt]) it becomes [tt]40[/tt];[/li]
[li]If it's between [tt]41[/tt] and [tt]60[/tt], inclusive, then if the value is less than [tt]50[/tt], then the value of [tt]d[/tt] becomes [tt]40[/tt], otherwise (if it's greater than or equal to [tt]50[/tt]) it becomes [tt]60[/tt];[/li]
[li]...and so on;
[/ol]
[li]Finally, combine the olf [tt]i[/tt] and the new value of [tt]d[/tt] to produce your rounded value;[/li]
[/ol]
Does that summarise it?

________________________________________
Roger J Coult; Grimsby, UK
In the game of life the dice have an odd number of sides.

 

[TonyJollans]

Hi chochoo,

If your value is in A1, then I think this should do it ..

=ROUND((A1/2),1)*2

It just halves the number and rounds as normal to the nearest 0.1, and then doubles the result.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[woja]

ToneyJollans
Wonderful.

Should have though of that myself, but let programming brain get in the way of maths brain.

Certainly desrves a star.


________________________________________
Roger J Coult; Grimsby, UK
In the game of life the dice have an odd number of sides.

 

[chochoo]

thanks tony yer formula is working