Round off.

[aratrika]

Kindly help me out that i have a cell value Rs.9957 in A1 and i want to load 5%-5% and to be rounded off to nearest 50 rupee. In this case the value will be Rs. 10481(9957/.95+9957) and i want the value to be rounded to 10500 and if the value is 10449 then rounded off to 10400. pls help me.


Thanks & regards


Aratrika

 

[NARAYANK991]

Hi Aratrika ,


I do not know how you got 10481 from 9957 , but the rounding off can be done by specifying a negative number ; =ROUND(10449,-2) will give 10400 ; =ROUND(10450,-2) will give 10500.


Narayan

 

[vasim]

Maybe you should have a look at Ceiling/Floor formulas

 

[aratrika]

I WANT THE CELL VALUE WHATEVER IT MAY BE THE AMOUNT TO BE RESTRICTED TO THE NEAREST 100 RUPEE. PLS. HELP.


THANKS & REGARDS


ARATRIKA

 

[sgmpatnaik]

@aratrika


Hi


is it possible to upload a sample workbook


Thanks


SP

 

[Faseeh]

Hi aratrika,


I think Narayan's formula is best if you always want to "Round Down" the figure to nearest 100th, but if you want to do "Round Up" as well depending on whether the fraction is less then 50 or more then it (eg. 10510 should be 10500 and 10551 & 10598 should be 10600) then this will work:

=IF(A1-ROUND(A1,-2)<51,ROUND(A1,-2),ROUND(A1,-2)+100)

Regards,

 

[shrivallabha]

Narayan's formula handles both cases and is shorter as well.


I guess Aratrika has not posted correct correlation to get 10481.


Following is Narayan's formula with Aratrika's requirement (hopefully )


=ROUND(A1/0.95,-2)

 

[Faseeh]

@ shrivallabha: Hopefully !!


Faseeh

 

[aratrika]

Thanks shrivallabha. It is working.