Nested if function question.

[Reborom]

I have problems that requires us to calculate Gross Pay using nested if statements. Column A is a named range called Pay_Grade that has values R1, R2, and R3, which correspond to HourlyRate1,HourlyRate2, and HourlyRate3. I need a nested if function to determine which hourly rate will be used for each employee.

Gross pay is calculated by taking the Total Hours worked X the Hourly Rate [HourlyRate1, HourlyRate2, or HourlyRate3] + Over Time hours X Hourly Rate X .5.

The Hourly Rate is determined by comparing each employees Pay Grade (R1, R2 or R3 in column A) with the Pay Grade in the Pay Rate Table. If the pay grade is R1 then the hourly rate is HourlyRate1.

I've tried to do a portion of it by entering: =IF(Pay_Grade="R1",HourlyRate1*Total_Hours,0), but it gives me a SPILL error.

What am I doing wrong?

 

[dreid1011]

I have problems that requires us to calculate Gross Pay using nested if statements. Column A is a named range called Pay_Grade that has values R1, R2, and R3, which correspond to HourlyRate1,HourlyRate2, and HourlyRate3. I need a nested if function to determine which hourly rate will be used for each employee.

Gross pay is calculated by taking the Total Hours worked X the Hourly Rate [HourlyRate1, HourlyRate2, or HourlyRate3] + Over Time hours X Hourly Rate X .5.

The Hourly Rate is determined by comparing each employees Pay Grade (R1, R2 or R3 in column A) with the Pay Grade in the Pay Rate Table. If the pay grade is R1 then the hourly rate is HourlyRate1.

I've tried to do a portion of it by entering: =IF(Pay_Grade="R1",HourlyRate1*Total_Hours,0), but it gives me a SPILL error.

What am I doing wrong?

Spill error is because there are values in adjacent cells where that formula is trying to display it's results (usually in the cells below it).

You could try something like this:

=LET(n,LOOKUP(Pay_Grade,{R1,R2,R3},{HourlyRate1,HourlyRate2,HourlyRate3}),Total_Hours*n+OT Hours*n*.5)