Er. Lalit Khandelwal who is working as Chief Instructor in Zonal Electric Training Centre, Western Railway, Vadodara, has brought to our notice a discrepancy in the Pay Fixation Chart posted on IRTSA Website. The discrepancy pointed out by him is very much valid in view of the clarificatory orders regarding “rounding off” of the amount of Increment of 3%, issued by the Railway Board vide its RBE No.28/2009 dated 11/2/2009.

It has been clarified at S.No. 4 of the said letter that, and here I quote:

(The query was):

Methodology for rounding off:

As per Rule 9 of the notification, the rate of increment in the revised pay structure will be 3% of the sum of the pay in the pay band and grade pay applicable, which will be rounded off to the next multiple of 10. Whether rounding off to next multiple of 10 has to be done in terms of rupees or even a paisa has to be rounded off. For example, if the pay after drawal of increment works out to Rs.10510.10 the same has to be rounded off to 10520 or 10510.”

The clarification provided is as follows:

“In the case of Board’s letter of even number dated 11.09.08 and 12.09.08 rounding off has already been done and the same should be implemented without any modification.

In the case of calculation of increments under the revised pay structure, paise should be ignored, but any amount of a rupee or more should be rounded off to next multiple of 10. To illustrate, if the amount of increment comes to Rs.1900.70 paise, then the amount will be rounded off to Rs.1900; if the amount of increment works out to be Rs.1901, then it will be rounded off to Rs.1910.”

The methodology applied in the Pay Fixation Excel charts prepared by us:

Since the Excel Charts were prepared by us, much before the above clarifications were issued, we had used the following syntax for rounding off of the amount of increment:

=CEILING((INT($E$31)+($E31+$H$13)*3/100),10)

As would be obvious to those who are conversant with the Excel formulae, the above syntax would provide a result of rounding off the value to the next ten rupees. But now the position has changed, in view of the clarificatory letter brought to our notice.

But the up and down rounding off of the value, i.e. to 1900 if the amount of increment works out to Rs.1900.70 and 1910.00 if it works out to Rs.1901.00, is posing some problem. Therefore, we are posting this query so that through discussion/ debate and online sources, we may be able to find a solution to the problem.

I have been looking around for a solution to the problem, but have not so far found any. Of course MROUND does come in handy but that is good enough for only one side rounding, i.e. either up or down but not for "IF" or "ELSE" like situations. I have a feeling that the solution may be available if someone, who, besides being an Excel exponent, is also into VB, would be able to find a solution for this particular problem.

By the way, I have checked up the following sites:

http://spreadsheets.about.com/b/2009/04/15/excel-2007-round-function-2.htm
http://www.vertex42.com/ExcelTips/significant-figures.html
and
http://www.maysstuff.com/excel/roundfun.html

But the desired solution is still eluding. Let’s all put our brains together to find a solution. All the best !

Dear Sir,

Formula is working perfect. Excellent initiative to solve my problem.

Thanks for quick reply.

With regards,
Lalit Khandelwal