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/...tion-2.htm
http://www.vertex42.com/ExcelTips/signif...gures.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 !

Similar debates have been going on at the following Links:

http://gconnect.in/forums/showthread.php?t=530
http://gconnect.in/forums/showthread.php?t=432
http://gconnect.in/forums/showthread.php...g#post2732

Our very close associate, Er. Debashis Mukhopadhyay, from Kolkata, has found a solution to the problem being faced in the rounding off of the amount of increment as delineated in the main post.

Formula already used: =CEILING((INT($E$31)+($E31+$H$13)*3/100),10).

Change suggested by Er. Debashis:

=CEILING((INT(TRUNC(($C$24)+($C$24+$F$6)*3/100))),10)

We are confident that the TRUNC will do the trick of truncating the decimals, if any arrived at within the parenthesis, and the rounding, where required, will be to the next multiple of rupees ten.

Yes, it does yield the desired result of Rs.27010 instead of 27020, when we wish to obtain increment of 3% in the aggregate amount of 30690 (26090+4600), but do not wish to round it to the next multiple of ten, since the amount of Rs.920.70, is required to be rounded off to Rs.920 only, as per latest clarificatory orders.

Congratulations, Er. Debashis, and thanks for the pains taken to find the solution, and that too in record time.

The latest Spreadsheet, wherein the above revised formula has been incorporated, can be downloaded from here.

And here is a note of thanks:

Dear Sir,

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

Thanks for quick reply.

With regards,
Lalit Khandelwal

Try using this too, where C36 is the pay in the pay band and D36 is the gradepay
=IF(MOD(INT((C36+D36)*3%),10)>0,CEILING((C36+D36)*3%,10),INT((C36+D36)*3%))+C36

Regards
Sundar

Regarding calculation of fixation.
Recent circulars,for common grade of previous 6500/- and 7450/-they used the word upgrade instead of merger. Actually, calculation method of upgraded and merged scale is different. Please clarify.

Try this too
CEILING((INT((C10)+(C10+C11)*3%)),10)
where c10 is Band Pay & C11 is grade pay.
Members may use it.
Regards
Lalit Khandelwal