IF (possibly OR) Formula assistance - multiple conditions

[kschmit1]

Hello,

I have made many attempts using a variety of IF, AND, OR combination statements to try and yield the results i'm looking for but have only partially or completely failed so i'm hoping someone can lend me a hand and let me know what i'm missing here.

I have three basic conditions on the attachment to yield a result in cell B32, they are as follows:

If B29 = Y, then B32 = $600
If B29 = N, then B32 = $300
If B29 = N/A, then B32 = ""

That part is relatively simple for me to figure out, it's when I add these extra elements that I'm having trouble stacking formulas to get the results I need which are the following:

If B27 = Exempt, then B32 = ""
If B27 = NonExempt and If B29 = Y, then B32 = $600 OR If B29 = N, then B32 = $300
If B27 = Mixed and If B29 = Y, then B32 = $600 OR If B29 = N, then B32 = $300 OR if B31 = B13, B32 = ""

Not really sure how I can combine all that above into a single formula or if this is wishful thinking

Thanks ahead of time!

[etaf]

If B29 = Y, then B32 = $600
If B29 = N, then B32 = $300
If B29 = N/A, then B32 = ""

Working backwards

B32 result is - ""
if
B29 = N/A OR B27 = Excempt OR B31 = B13

B32 result is $300
IF
B29 = "N" is the only option

Otherwise , i dont understand your logic

[aditya88]

=IF(B29="Y",600,IF(B29="N",300,IF(OR(B29="N/A",B27="Exempt",B31=B13),"",IF(AND(B27="NonExempt",B29="Y"),600,IF(AND(B27="NonExempt",B29="N"),300,IF(AND(B27="Mixed",B29="Y"),600,300))))))

above formula meets most requirements but you need to clarify some things.

Ie you say that If B29 = Y, then B32 = $600, but you also say that if B31=B13, then B32="", and in the spreadsheet you have attached both those are true so you cannot have both those scenarios execute. Most likely what you need to do is say that if B13=B31, then B29 cannot equal Yes.

Same with if B27 = Exempt, where if B29=Y, which takes precedence? I assume same where if B27=Exempt then B29 should be auto set to "N/A". You have to do dependent lists in order to accomplish the above things.

If the form is internal (ie only you will use it) and you will auto set B29 to the correct value then you don't have to worry about the above things but if multiple people are using it, you have to fix it. I would recommend fixing it even if you're the only one using it so you don't make a mistke.

[CK76]

Try formula below.

=IFERROR(CHOOSE(MATCH(B29,{"N/A","Y","N"},0)*(B27<>"Exempt")*(B31<>B13),"",600,300),"")

[kschmit1]

thanks CK, this did the trick!