percentage round up to nearest decimal point with range

[ahjeff86]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Salary[/TD]
[TD]11%[/TD]
[TD]Payroll Website Result[/TD]
[/TR]
[TR]
[TD]RM 3460[/TD]
[TD]RM 380.60[/TD]
[TD]RM 381.00[/TD]
[/TR]
[TR]
[TD]RM 3470[/TD]
[TD]RM 381.70[/TD]
[TD]RM 383.00[/TD]
[/TR]
[TR]
[TD]RM 3480[/TD]
[TD]RM 382.80[/TD]
[TD]RM 383.00[/TD]
[/TR]
[TR]
[TD]RM 3490[/TD]
[TD]RM 383.90[/TD]
[TD]RM 385.00[/TD]
[/TR]
</tbody>[/TABLE]









dear all,


I'm doing a manual excel formula payroll therefore I faced an issues that the calculation of 11% couldn't be the same as the payroll website.


1st Question:
If I used =A1*11% i will get the result as in table column 11% but when I check in the website it shows different amount. It seems to go to the nearest +2 figure, u can notice RM 3470 & RM 3480 shared the same amount of RM 383, but excel give me different amount. May I know is there any formula in excel can achieve the result?

2nd Question:
If the result can be achieved, I need the format to be RM 383.00 with two decimal point figure.

Any advise for this will be much appreciated.

 

[dreid1011]

Welcome to the board.

Do you know if the payroll website is manually entered data or done with a program? Now, I'm only one person, but that 381.70 rounding up to 383 just doesn't make sense. I suspect it's a typo or error of some sort. However, if it is indeed intentional, perhaps we can come up with something to achieve that result.

Are there other pay rates on the website that reflect the odd rounding, or is it just that one instance? If there are more than one instance of the odd result, we would need to know how or why to determine when to round up so much.

 

[ahjeff86]

Welcome to the board.

Do you know if the payroll website is manually entered data or done with a program? Now, I'm only one person, but that 381.70 rounding up to 383 just doesn't make sense. I suspect it's a typo or error of some sort. However, if it is indeed intentional, perhaps we can come up with something to achieve that result.

Are there other pay rates on the website that reflect the odd rounding, or is it just that one instance? If there are more than one instance of the odd result, we would need to know how or why to determine when to round up so much.

Hi there,

The website I believed it's coded with program. You can have a look at here. http://payroll.my/

Because the saving doesn't come with cents at behind so, we round it up. as for the results, is it possible to achieve using formula?

I was thinking to use nested IF but it seems impossible as the salary is ranging 2000 to 5000.. it give me no choices but to seek advise from experts at here.

 

[ahjeff86]

My apologize, I just founded it's every RM 20 increase of RM 2.
You can see above image attachment and you can see the range. Is there any possibilities this to be done in excel?

 

[dreid1011]

Are you required to use that payroll site? It seems it's got some quirk to it's rounding and I can't figure it out. Even the 10% option was off on the result for 3490. Instead of returning 349, it rounded to 350. The other three values returned 346, 347 and 348 as expected.

Could you just use Excel and not match it up with that site? Unless someone else has some idea, I think it's going to be more trouble than it's worth to try and figure it out.

 

[dreid1011]

My apologize, I just founded it's every RM 20 increase of RM 2.
You can see above image attachment and you can see the range. Is there any possibilities this to be done in excel?

A couple of those increase by 3. 385 to 388 and 396 to 399.

 

[ahjeff86]

A couple of those increase by 3. 385 to 388 and 396 to 399.

Seems it's kinda hard to do it unless we made a table with answer and search based on the range?

 

[dreid1011]

That would probably be the easiest. The only thing I can determine from that chart is that the increase of 3 happens at the ranges beginning a new 100 (ie: 3500.01 is 3 more than previous, and 3600.01 is 3 more). So four times it increases by 2, then the 5th is 3. Maybe that is it? That might be something we can work with.

 

[ahjeff86]

Actually I just converted the pdf to excel, and now I have the column A with the beginning range while the column B is the ending range and column C is the contribution rate.

For example: E1 salary is RM 3456.90, how can I achieved to let column E1 to run through Column A and B to get the correct contribution from Column C?

 

[ahjeff86]

I can't post the excel file maybe due to I'm new user? anyway I attached the screenshot at here.

How can I achieved as what I mention above for column F2