Compare two cells and determine one of three actions to return a value

[Novice Excel User]

What I need is a formula to compare the frequency (hourly/weekly) in D8 and D9 and perform one of the following:

• If D8 is Hourly and D9 is weekly then multiply C15 by E8
• If D8 is weekly and D8 is hourly then divide C15 by E8
• If D8 and D9 are the same (both are either hourly or weekly) then return the value in C15 as is

Essentially I need the salary in C15 to reflect the appropriate frequency in order to calculate what the new salary at the correct frequency, either hourly or weekly.

Here's a couple formulas I have tried, but don't seem to work fully:

=IF(AND(G8="hourly", G9="weekly"), F15*H8, IF(AND(G8="weekly", G9="hourly"), F15/H8, F15))

=IFS(D8=D9, C15, AND(D8="Hourly", D9="Weekly"), C15*E8, AND(D8="Weekly", D9="Hourly"), C15/E8)

 

[rlv01]

See if this works

=IF(AND(D8="hourly", D9="weekly"),C15*E8,IF(AND(D8="weekly", D9="hourly"),C15/E8,IF(OR(AND(D8="hourly", D9="hourly"),AND(D8="weekly", D9="weekly")),C15,"Error")))

 

[Novice Excel User]

Thanks, but this only returns "Error" when I enter it into my spreadsheet, regardless of what the other cells it's looking at have in them. I need to it return a number/salary.

 

[Carl Stephens]

Try this one.

=IF(D8="Hourly",IF(D9="Weekly",C15*E8,C15),IF(D9="Hourly",C15/E8,C15))

 

[Novice Excel User]

=IF(D8="Hourly", IF(D9="Weekly", C15*E8, C15), IF(D9="Hourly", C15/E8, C15))

Thank you! This only seems to recognize/"action" if the frequency hourly; returning an hourly salary rate regardless of what is in D8 and D9.

 

[rlv01]

Thanks, but this only returns "Error" when I enter it into my spreadsheet, regardless of what the other cells it's looking at have in them. I need to it return a number/salary.

It returns 'Error' if none of the conditions you specified are true. I had to guess a bit because of inconsistent information in your post. I think you may need to clean up your conditions and re-post. For example, here you say that D8 and D9 are the cells of interests:

What I need is a formula to compare the frequency (hourly/weekly) in D8 and D9 and perform one of the following:

• If D8 is Hourly and D9 is weekly then multiply C15 by E8
• If D8 is weekly and D8 is hourly then divide C15 by E8
• If D8 and D9 are the same (both are either hourly or weekly) then return the value in C15 as is

but then you post formulas that use G8 and G9 , instead of D8 and D9 as you first said.

Here's a couple formulas I have tried, but don't seem to work fully:

=IF(AND(G8="hourly", G9="weekly"), F15*H8, IF(AND(G8="weekly", G9="hourly"), F15/H8, F15))

=IFS(D8=D9, C15, AND(D8="Hourly", D9="Weekly"), C15*E8, AND(D8="Weekly", D9="Hourly"), C15/E8)

Do you think you can revise your requirements to make things consistent?

 

[Novice Excel User]

Not sure if this has any impact but the values in D8 and D9 are based on a vlookup formulas that pull from a table in a separate worksheet within the same workbook. Could the formulas in those fields be impacting the affectedness of this formula?

 

[rlv01]

It should not, but you should use static values for D8 & D9 until you are sure your formula(s) are working. Once they are go back and replace with VLookup and test further.

 

[Carl Stephens]

=IF(AND(D8="hourly",D9="weekly"),C15*H8,IF(AND(D8="weekly",D9="hourly"),C15/H8,IF(D8=G9,C15,"")

 

[Novice Excel User]

=IF(AND(D8="hourly",D9="weekly"),C15*H8,IF(AND(D8="weekly",D9="hourly"),C15/H8,IF(D8=G9,C15,"")

This one isn't calculated; it shows the formula in the cell, as if there is no equals sign in front, even though it is (copied and pasted into the spreadsheet).