percentage round up to nearest decimal point with range

[ahjeff86]

Salary	11%	Payroll Website Result
RM 3460	RM 380.60	RM 381.00
RM 3470	RM 381.70	RM 383.00
RM 3480	RM 382.80	RM 383.00
RM 3490	RM 383.90	RM 385.00

<tbody>
</tbody>

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.