Excel payroll work [Solved/Closed]

Winndixie 20 Posts Monday March 10, 2014Registration date June 5, 2015 Last seen - Mar 11, 2014 at 01:50 AM - Latest reply: Winndixie 20 Posts Monday March 10, 2014Registration date June 5, 2015 Last seen
- Mar 12, 2014 at 01:47 AM
Hello,


I'm using Excel to do payroll system.

e.g.

I create worksheet that showing data as below table :-

basic more than 0.01 less than 10.00 = 0
basic more than 10.01 less than 20.00 = 3.00
basic more than 20.01 less than 40.00 = 6.00
until 20,000.00

I using a code for my table as (1) for more than 0.01 less than 10.00 = 0.... till 20,000.00 and created 300++ code for the table

for my calculation when my basic showing 10.50 should show as 3.00 if more than 20.01 showing 6.00

now I using a formula =LOOKUP(A1,data!A4:A805,date!D4:D805)...A1 was the column to add code, so that not very useful for my payroll work. every time the basic changed I need to refer the table that I create and get the code.

how do I create a formula when I changing my basic then my data can read the value as need?
See more 

4 replies

Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 11, 2014 at 09:05 AM
0
Thank you
Winndixie, Good morning.

Sorry, but I didn't understand your question.

To easier things, save your Excel file at a free site, www.speedyshare.com and put the link to download here.
When we see your layout, the suggestion comes better.

While we're waiting your excel file, please, take a look at an example I did for you.
http://speedy.sh/ad2fG/11-03-2014-en-Kioskea-Reference-Table-Payroll.xlsx

Is this what you want?
I hope it helps.
Belo Horizonte, Brasil.
Marcílio Lobão
Winndixie 20 Posts Monday March 10, 2014Registration date June 5, 2015 Last seen - Mar 11, 2014 at 08:35 PM
0
Thank you
Good day!!

here I attachment my file :)

http://speedy.sh/hrEZP/Payroll-Report-2014.xls
Mazzaropi 1834 Posts Monday August 16, 2010Registration dateContributorStatus May 30, 2018 Last seen - Mar 11, 2014 at 11:10 PM
0
Thank you
Winndixie, Good evening.

I downloaded your Excel file.
I took a good look on it.

Observations: (My opinion).
a) The tabs EPF and SOCSO have a Structural Problem with the table.
a.1) Merged cells.
a.2) Repeated values on a ordered list.
a.3) Missing conditions for value EQUAL to limit.
These aspects make the automatic search a task almost impossible.

b) These tabs present an unnecessary calculus on cells.
b.1) F4 = D4 E4
b.2) G4 = D4-E4
Is not necessary to have F4 and G4 at your Data Bank.
If on any moment you need these calculus, use then in the formula when necessary.
A lot of unnecessary formulas makes your Excel file bigger and slower, without give you any benefit.

I have a suggestion for you.
I created a tab "NEW SOCSO".
The first 10 codes were reorganized.

At tab "Payroll Report-Jan (1)", I introduced a new formula and two new records to show you as the formula and a new table at "New SOCSO" works.

I did this as an example for you.
If you like it, you can reorganize all your table at both: "SOCSO" and "EPF" tabs.
Here it is:
http://speedy.sh/BXPcx/11-03-2014-en-Kioskea-Payroll-Report-2014.xls

Make your tests.

Please, tell us if it worked for you.
I hope it helps.
Winndixie 20 Posts Monday March 10, 2014Registration date June 5, 2015 Last seen - Mar 12, 2014 at 01:47 AM
GOOD DAY!! :)

That was very helpful for my payroll work!! :D

Thank you very much !!