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]

You'll want to use a VLOOKUP like so:

=VLOOKUP(E1,$A$2:$C$10,3,TRUE)

 

[ahjeff86]

You'll want to use a VLOOKUP like so:

=VLOOKUP(E1,$A$2:$C$10,3,TRUE)

It works like charm!

What if my salary E1 is from another Sheet and the data is from sheet name "EPF" how can I use VLOOKUP to another sheet?

 

[dreid1011]

Depends on where you put the VLOOKUP.

You either do

=VLOOKUP(sheetname!E1,$A$2:$C$10,TRUE) <- VLOOKUP located on sheet EPF

Or

=VLOOKUP(E1,EPF!$A$2:$C$10,TRUE) <- VLOOKUP located on the other sheet with E1

If your other sheet name has spaces, then you must do this: 'sheet name'! with single quotes.

 

[ahjeff86]

thanks so much for your help! appreciated it, it works like charm.

cheers~

 

[dreid1011]

You're welcome.