Simplify this Formula

[Thomas Kuriakose]

Respected Sirs,

We have a list of values which need to be rounded to the nearest 1000 and 100.

Kindly find attached a file where we have used the below formula - Kindly let me know whether this is correct and this can be simplified.

Thank you very much,

with regards,
thomas

 

[NARAYANK991]

Hi ,

See if this works :

=IF(A2 >= 1000, MROUND(A2,1000), MROUND(A2, 100))

Narayan

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this solution.

Sir, how can we get the round of lowest value in this list,

for example - 475 should be 400, 37,500 should be 37,000.

Much much appreciated,

with regards,
thomas

 

[bosco_yip]

1] This is your specification noted inside the file :

6 Digits Round to nearest 1000
5 Digits Round to nearest 1000
4 Digits Round to nearest 1000
3 Digits Round to nearest 100

2] But, your example data result in B6:B7 and B9:B15, these 5 digits number round to nearest 100, Please clarify !

3] Then based on [1] specification, B2 formula copied down :

=FLOOR(A2,1&REPT(0,2+(LEN(INT(A2))>3)))

Regards
Bosco

 

[NARAYANK991]

Hi ,

If you just want to remove the value beyond the hundreds part , try this :

=TRUNC(A2 , -2)

Narayan

 

[NARAYANK991]

Hi ,

Going by the example you posted , use an IF to differentiate between numbers less than 1000 , and those beyond :

=IF(A2 >= 1000, TRUNC(A2,-3), TRUNC(A2,-2))

Narayan

 

[Thomas Kuriakose]

Respected Sirs,

Thank you so much for all the solutions provided.

My mistake, the formula we used did not consider 5 digits, we wrote only for 4, 4 and 3 and missed out 5.

The requirement was round upto nearest and lowest.

475 to 500 as well as 475 to 400

Thank you so much once again,

with regards,
thomas