Converting Multiple IF function instances to column formula

Idea Constructions

Hi,

I am trying to set up time sheet for employees where formula in the column will calculate their income for hours worked.

Employees are using form to enter their hours.

I have set up their individual hourly rates in the summary sheet.

"Amount Earned" Column contains formula. Formula is:

=IF(Name1 = "Worker1",[Work Hours]@row * [Worker1 Hourly Rate]#, IF(Name1 = "Worker2",

[Work Hours]@row * [Worker2 Hourly Rate]#, IF(Name1 = "Worker3", [Work Hours]@row * [Worker3 Hourly Rate]#)))

Because my formula contains @row function, I can't convert it to column formula.

I tried setting up another 2 helper columns with employee names and hour rate, but again i end up using @row function.

Would anyone have an idea how to deal with this?

Nick Korna

Hi @Idea Constructions

If you have your worker names and hourly rate on either on another sheet or on this one, you can use an INDEX or VLOOKUP to grab the data. This would remove the need for a massive nested IF statement.

For example, if you had the names/rates on the same sheet with them next to one another (and obviously not visible on the form):

Where the formula in Amount Earned is:

=[Work Hours]@row * VLOOKUP(Name@row, [Employee Name]:Rate, 2, false)

The same result, but in a slightly different way of doing it is in Amount Earned 2:

=[Work Hours]@row * INDEX(Rate:Rate, MATCH(Name@row, [Employee Name]:[Employee Name], 0))

You could also do this with a cross sheet reference storing the names & rates on another sheet. If you have other data then the index method makes things easier as you wouldn't need to count columns if not adjacent etc.

Hope this helps, but if I've misunderstood anything or you have any problems/questions then just let us know!

Idea Constructions

Thanks for your help Nick.

It worked perfectly.