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)

 

[Carl Stephens]

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

 

[Novice Excel User]

I noticed that the cells in the last suggestion were a little off so changed the correct cells: =IF(AND(D8="hourly",D9="weekly"),C15*E8,IF(AND(D8="weekly",D9="hourly"),C15/E8,IF(D8=D9,C15,"")))

When entered, it seems to clear the cell, including the formatting.

 

[Novice Excel User]

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:



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


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

Sorry for the confusion! The spreadsheet is evolving as I try to work this formula out. The cells in my initial post are correct. In other words:

Frequency (Hourly or Weekly) are in D8 and D9, which need to be compared.
Standard hours (36.25 or 40.00) are in E8, which is the multiplier/divider, depending on what the frequency is.
The current salary is C15 (manually entered) and this is the value that needs to be multiplied/divided if needed depending on the frequency.
D15 is where the formula will be entered, in order to return the salary based on the frequency. D15 needs to reflect what is in C15 as is, if D8 and D9 are the same OR C15*E8 if D8 is hourly and D9 is weekly OR C15/E8 if D8 is weekly and D9 is hourly.

Hope that makes sense!

Appreciate everyone's time!

 

[Novice Excel User]

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:



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


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

Sorry about the confusion! The cells provided in my original post are accurate.

D8 and D9 hold the frequency (hourly or weekly), which need to be compared.
E8 holds the standard hours (36.25 or 40.00) which need to be multiplied or divided, if applicable, depending on the frequency.
C15 is the salary which is manually entered and is used multiplied/divided, if applicable, by E8, depending on the frequency.
D15 is the cell which the formula will be in, which needs to return the salary depending on the frequency/hours.

Hope this clarifies! Appreciate everyone's time!

 

[Novice Excel User]

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.

I removed the (vlookup) formula in D8 and D9 and typed in hourly and weekly and the formula {IF(AND(D8="hourly", D9="weekly"), C15*E8, IF(AND(D8="weekly", D9="hourly"), C15/E8, C15))} in D15 now works properly. Appears the formula in D8 and D9 might be the hiccup. Any ideas how to get around this? I need D8/D9 to pull information from a different worksheet.

 

[Carl Stephens]

I removed the (vlookup) formula in D8 and D9 and typed in hourly and weekly and the formula {IF(AND(D8="hourly", D9="weekly"), C15*E8, IF(AND(D8="weekly", D9="hourly"), C15/E8, C15))} in D15 now works properly. Appears the formula in D8 and D9 might be the hiccup. Any ideas how to get around this? I need D8/D9 to pull information from a different worksheet.

Attached the sheet if you don’t mind sharing and remove any personal data if there is any.

 

[Novice Excel User]

Attached the sheet if you don’t mind sharing and remove any personal data if there is any.

Unfortunately, I'm unable to share the data in the document. Thanks anyway.

 

[Novice Excel User]

Unfortunately, I'm unable to share the data in the document. Thanks anyway.

Maybe it will help if I provide the formulas that are in the cells that the problematic cell is pointing to?
Note: The table/worksheet these formulas are pointing to is titled AD102698. C8 and C9 referenced in the following formulas are manually entered (no formulas)
D8: =XLOOKUP(C8&" ",'AD102698'!$A$5:$A$3533,'AD102698'!$M$5:$M$3533,0,0,1)
D9: =XLOOKUP(C9&" ",'AD102698'!$A$5:$A$3533,'AD102698'!$M$5:$M$3533,0,0,1)
E8: =XLOOKUP(C8&" ",'AD102698'!$A$5:$A$3533,'AD102698'!$J$5:$J$3533,0,0,1)
C15 as previously noted is a manually entered salary (no formula)
D15 is the cell where the formula in question will go

 

[rlv01]

D8 and D9 might be the hiccup. Any ideas how to get around this?

Make sure you are not suffering from issues of case sensitivity or leading/trailing spaces returned by your vlookup formula.

=LET(Frequency,LOWER(TRIM(D8) & TRIM(D9)),IF(Frequency = "hourlyweekly",C15*E8,IF(Frequency="weeklyhourly",C15/E8,IF(OR(Frequency = "hourlyhourly",Frequency = "weeklyweekly"),C15,"Error"))))

 

[Novice Excel User]

Make sure you are not suffering from issues of case sensitivity or leading/trailing spaces returned by your vlookup formula.

=LET(Frequency,LOWER(TRIM(D8) & TRIM(D9)),IF(Frequency = "hourlyweekly",C15*E8,IF(Frequency="weeklyhourly",C15/E8,IF(OR(Frequency = "hourlyhourly",Frequency = "weeklyweekly"),C15,"Error"))))

This one seems to work, THANK YOU!!!