IF function trouble- is this the correct function?

taylor.hughes

I am attempting to use the IF function, but not sure if it makes sense in this scenario: If "Return visit" is identified as 'OT', I want to multiple the "Total qty Labor Hours" by $195. If the "Return visit" cell is NOT identified as 'OT', I want to multiple the "Total qty Labor Hours" by $130.

Here is what I have tried so far, with no luck:

=(IF([Return visit hours]="OT", [Total qty Labor Hours]@row*$195, [Total qty Labor Hours]@row*$130))

=(IF([Return visit hours]="OT", [Total qty Labor Hours]@row*195, [Total qty Labor Hours]@row*130))

=(IF([Return visit hours]=OT, [Total qty Labor Hours]*195, [Total qty Labor Hours]*130))

Any advice would be greatly appreciated!

Austin Smith

@taylor.hughes

You had the general gist of it correct.

Use summary data fields to store your regular and OT rate (especially because they will change over time).

Small page icon on the right side of your sheet.

=IF[return visit hours]@row="OT", [summary OT field]*[total qty labor hours]@row, [summary regular field]*[total qty labor hours]@row)

Format the column for Est Labor Hours Cost to text/number and select the dollar sign button in the ribbon at the top of the page. Dollar signs in formulae mean something very different.

Amber Eakin

Hi @taylor.hughes - You're just missing the @row function. Also, you start with the formula and not a parenthesis. Try this!

=IF([Return visit hours]@row="OT", [Total qty Labor Hours]@row*195, [Total qty Labor Hours]@row*130)

Does that work?

taylor.hughes

Thank you, both! I was impatient and just ended up creating another column to automatically relay the rates associated with each option (OT and Regular Business Hours) using:

=IF([Return visit hours]@row = "Overtime", 195, 130)

I then multiplied the rate by the total labor hours. That way when OT or regular business hours is selected, the formula automatically populates using the correct rate:

=[Total qty Travel Hours]@row * [Hourly Rate]@row

I will hide the Hourly rate column as it is just for formula purposes.