Rounding Decimals

Hi,
Good morning all, hope everything is well!
I'm living in the Middle East "Kuwait". Here when we are calculating the amount we are using 3 decimals after comma such as (100.623, 47.187, 249.446, 8075.343, 642.508... etc).
I need to know, in excel how can I around the only decimals not whole amount. I have to type of rounding, for examples:
Type 1:
Example 1: 54.250 keep it same 54.250
Example 2: 54.251 round to 54.250
Example 3: 54.252 round to 54.250
Example 4: 54.253 round to 54.250
Example 5: 54.254 round to 54.250
Example 6: 54.255 keep it same 54.255

Type 2:
Example 1: 155.375 keep it same 155.375
Example 2: 155.376 round to 155.380
Example 3: 155.377 round to 155.380
Example 4: 155.378 round to 155.380
Example 5: 155.379 round to 155.380
Example 6: 155.380 keep it same 155.375

To make it more clear, my point is Type 1: any last decimals (from 1-4) round it to "0", Type 2: any last decimals (from 6-9) round it to "1".
If last decimal is 0 or 5 just keep it as it is such as (11.250, 46.895, 425.110, 201.640, 5.115, 25.160... etc)

I hope everything is clear now for you.

could you please help me how can I use the right formula in Excel without using micro?

Best Wished,

Mostafa Saqallah

Hi Mostafa, perhaps a formula like this.

Formula:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi,

For the first set use the following formula:

=FLOOR(A1,0.005)

&

For the second set:

=CEILING(A20,0.005)

See the attached file.

Originally Posted by Glenn Kennedy

Ali, that last one doesn't work. It's rounding to nearest 0.0025. Your formula at 3 is good, though it might be a bit neater to use

=IF(MOD(A1,0.01)<0.005,FLOOR(A1,0.005),CEILING(A1,0.005))

Hi Glenn, I seem to have overlooked the description provided by OP on Type 1 & Type 2 ( I thought these are two different data sets)

your formula should work,

=IF(MOD(A1,0.01)<0.005,FLOOR(A1,0.005),CEILING(A1,0.005))