IF(OR formula

[Marq]

Hello,

I am trying to write a formula that is an IF(OR

below is what I have:

=IF(OR(D5="Yes",D4="Yes",$K$5,IF(OR(D5="No",D4="No",$K$3,IF(OR(D5="PENDING",D4="PENDING",""))) which does not work

I also tried below, which gives me a #VALUE result

=OR(D5="Yes",D4="Yes",$K$5,OR(D5="No",D4="No",$K$3,OR(D5="PENDING",D4="PENDING","")))

I am trying to say this with the formula (formal written in cell D66):

If the drop down result in either D4 OR D5 equals YES, then populate this cell with the value that is in K5...or if the drop down result in either D4 or D5 is NO then populate this cell wit the value that is in K3....or if the drop down result in either D4 or D5 is PENDING then leave this cell blank.

basically if either cells D4 or D5 contain a yes then populate the cell with K5..............the only way the cell will be populated with K3 is if both D4 and D5 are "NO".

 

[RoryA]

You haven't closed the OR functions. It should be:

=IF(OR(D5="Yes",D4="Yes"),$K$5,IF(OR(D5="No",D4="No"),$K$3,IF(OR(D5="PENDING",D4="PENDING"),"")))

 

[Marq]

You haven't closed the OR functions. It should be:

=IF(OR(D5="Yes",D4="Yes"),$K$5,IF(OR(D5="No",D4="No"),$K$3,IF(OR(D5="PENDING",D4="PENDING"),"")))

it works!!..thanks!..i was missing those ")"'s, correct?

 

[RoryA]

Yes, that's correct.

 

[Scott T]

If you click on an augment in the screen tip it will select the augment in your formula. This can help you troubleshoot your problem as you can clearly see the ")" is not in the right place as it select everything instead of just the OR statement.

 

[Marq]

Ok..I want to add one more thing to the formula...currently it is choosing straight time rates in K3 and K5....I now want to intergrate using the OT rates in K4 and K6

I made another drop down cell in D3. The only options are ST and OT.

If I chose ST in the drop down, then the formula you corrected for me wouldn't change the result....BUT if i pick OT then I need it to read K4 or K6...(K4 for all "No's" in D4 & D5...and K6 if either or both D4 & D5 have a "Yes".

basically i just want to change the rates based on the D3 choice of ST or OT....each ST and OT have two rates..so there are two different rates for ST and two different rates for OT.

 

[Marq]

so, I'm saying if D3 is ST then the formula you corrected should calculate K3 or K5....if D3 is OT, then the formula you corrected for me should focus on K4 and K6

 

[Marq]

here is pic of the drop down cells (D3 through D6)...and the rates (K3-K6)

I have default choices in D3-D6

These are the choices in drop downs:

D3: ST or OT
D4: Yes, No or PENDING
D5: Yes, No or PENDING
D6: Yes, No or PENDING

 

[Marq]

here is what i have only as an "IF" formula...i get a "FALSE" result.

=IF(D3="OT",IF($D$4="Yes",$K$6,IF($D$4="No",$K$4,IF($D$4="PENDING",""))))=IF(D3="ST",IF($D$4="Yes",$K$5,IF($D$4="No",$K$3,IF($D$4="PENDING",""))))

I'll worry about the IF(OR later...for now im trying to get this one working

 

[Scott T]

Try

=IF(D3="st",IF(OR(D5="Yes",D4="Yes"),$K$5,IF(OR(D5="No",D4="No"),$K$3,IF(OR(D5="PENDING",D4="PENDING"),""))),IF(OR(D5="Yes",D4="Yes"),$K$6,IF(OR(D5="No",D4="No"),$K$4,IF(OR(D5="PENDING",D4="PENDING"),""))))