I'm doing a project for a computer class and I have a group of hypothetical employees.

There are three columns: employee, hours worked, and wage. Is there a way to format the employee's name so that when I type it and hit enter, it calculates their hours worked for that day multiplied by their hourly rate?

Any help would be greatly appreciated!! Thanks!

Like this?

Attached Files

Wage.xlsx (13.8 KB, 19 views)

Sort of. I did think about having a column for hourly rate and formatting the wage column to calculate hours*hourly rate, however I am also having columns for things like overtime, sick, and being late. I wanted to keep the table as streamlined as possible, minimize the amount of columns, if that makes sense? I also thought I could have their wage rate on a separate sheet and have the formula for wage reference the info on that sheet. Ideally though, I would like to be able to type in the name of the employee and that name is formatted to calculate their hourly rate by referencing the hours listed in a given row. I know you can name a cell or a group of cells, but couldn't figure out how to attribute a formula to a name that you type into a cell. I don't know if this is possible but it would make things so much easier if it were!!

Try to set up your sheet as you want it, upload the file and I shall see if I can help with a formula (please mention if you enter hours as for example 7 instead of 7:00).

That would be awesome, thank you! I have a busy weekend, so I'll probably upload it tomorrow or Monday

Here you go. Thanks again!

Attached Files

Payroll Project.xlsx (18.4 KB, 17 views)

Take a look at the attached.


Edit:

I forgot to multiply by hours so the formulas in F5 and G5 should be:

=IF([@Hours]="","",INDEX(Rates!$B$4:$B$8,MATCH([@Employee],Rates!$A$4:$A$8,0))*[@Hours])

=IF([@Hours]="","",INDEX(Rates!$C$4:$C$8,MATCH([@Employee],Rates!$A$4:$A$8,0))*[@Hours])

Attached Files

Payroll Project_2.xlsx (21.3 KB, 13 views)

I am so sorry for this late reply! I had two tests this week that monopolized my time and the notifications for this website go in my junk mail. I really appreciate your input on this, so thank you for the time you're spending trying to figure it out!!

Attached Files

Payroll Project_2.xlsx (21.5 KB, 8 views)

Is this better?

(Only change is that I have applied the formulas I stated in#7).

Attached Files

Payroll Project_3.xlsx (21.8 KB, 8 views)

That is exactly what I had in mind!! Ah, thank you! Now I just have two questions at the moment:
-If I need to add more employees, and let's say that takes it down to row 14 in the Rates table. I would then just change your formula to

Net pay =IF([@Hours]="","",INDEX(Rates!$B$4:$B$9,MATCH([@Employee],Rates!$A$4:$A$14,0))*[@Hours])

and

Billing
=IF([@Hours]="","",INDEX(Rates!$C$4:$C$9,MATCH([@Employee],Rates!$A$4:$A$14,0))*[@Hours])

right?

-Do you mind explaining this in case I need to do something similar in the future?

I reviewed our previous conversation, and amidst the busy week, I had forgotten #7. Lol thank you for adding the correction.

See comments in Payroll Log.

Attached Files

Payroll Project_3.1.xlsx (24.1 KB, 13 views)

Awesome, thank you! Your input really exceeded all expectations. I really appreciate the help. I've got a busy week again coming up, but looked at your corrections and suggestions. I'll play around with it Thursday or Friday and let you know if I have any final questions!

I have a general question, if you don't mind me asking. How did you become so good at excel? Was it a class or just through experience? I'm in a class now, and am vaguely familiar with the index function, name manager, etc., but nowhere near where I'm able to get creative and apply it to specific rules/tables/situations (..clearly).

Thanks again!