would an If-Then statement/formula solve this

%3CLINGO-SUB%20id%3D%22lingo-sub-1548631%22%20slang%3D%22en-US%22%3Ewould%20an%20If-Then%20statement%2Fformula%20solve%20this%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1548631%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20employee%20dept%2C%20rank%20and%20hours%20worked%20data%20on%20a%20spreadsheet%20similar%20to%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22212%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2284%22%3EDepartment%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Erank%3C%2FTD%3E%3CTD%20width%3D%2264%22%3Ehours%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EConstruction%3C%2FTD%3E%3CTD%3EManager%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDesign%3C%2FTD%3E%3CTD%3EManager%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EConstruction%3C%2FTD%3E%3CTD%3EIntern%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EDesign%3C%2FTD%3E%3CTD%3Eintern%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhat's%20not%20included%20in%20the%20spreadsheet%20is%20rate%20per%20hour.%20What%20I%20want%20to%20do%20is%20create%20a%20new%20column%20that%20calculates%20the%20total%20%24%20for%20the%20work.%3C%2FP%3E%3CP%3Eexample%3A%3C%2FP%3E%3CUL%3E%3CLI%3Econstruction%20manager%20earns%2050%24%2Fhour%3C%2FLI%3E%3CLI%3Edesign%20manager%20earns%2040%24%2Fhour%3C%2FLI%3E%3CLI%3Econstruction%20intern%20earns%2015%24%2Fhour%3C%2FLI%3E%3CLI%3Edesign%20intern%20earns%2010%24%2Fhour%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emy%20thinking%20(pls%20correct%20me%20if%20im%20wrong)%20is%20that%20an%20if%20then%20might%20work%20here%3F%20i%20dont%20know%20if%20its%20possible%20to%20have%20multiple%20if-then%20statements%20in%20a%20cell.%3C%2FP%3E%3CP%3Eso%3A%3C%2FP%3E%3CP%3Eif%20department%20%3D%20construction%20and%20rank%20%3D%20manager%2C%20then%20hours%20worked%20*%2050%3C%2FP%3E%3CP%3Eelse%20if%20department%20%3D%20design%20and%20rank%20%3D%20manager%20then%20hours%20worked%20*40%3C%2FP%3E%3CP%3Eand%20so%20on%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edoes%20this%20make%20sense%3F%20i%20am%20open%20to%20any%20other%20options%20for%20solution!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1548631%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

We have employee dept, rank and hours worked data on a spreadsheet similar to below:

 

Departmentrankhours
ConstructionManager3
DesignManager7
ConstructionIntern15
Designintern16

 

what's not included in the spreadsheet is rate per hour. What I want to do is create a new column that calculates the total $ for the work.

example:

  • construction manager earns 50$/hour
  • design manager earns 40$/hour
  • construction intern earns 15$/hour
  • design intern earns 10$/hour

 

my thinking (pls correct me if im wrong) is that an if then might work here? i dont know if its possible to have multiple if-then statements in a cell.

so:

if department = construction and rank = manager, then hours worked * 50

else if department = design and rank = manager then hours worked *40

and so on

 

does this make sense? i am open to any other options for solution!

1 Reply
Highlighted

@aceyac 

It's better to add range with rates at any place of your workbook as

image.png

and use formula

=IFNA($D4*INDEX($H$4:$I$5,MATCH($B4,$G$4:$G$5,0),MATCH($C4,$H$3:$I$3,0)),"wrong data")