Logical formula in Excel - Help needed

Question

I need help with an excel formula. I am trying to achieve the following behaviour:

• the formula is in cell A
• in cell B we can have 3 possible values - Low, Medium or High

So if value of cell B is equal to Low then in the A cell the value is "Yes" If the value in cell B is equal to Medium or High then the formula should check if cell C is populated and its content is different then "please fill in" or it is left "blank". If true then cell A is "Yes", if not, then it is "No".

If value of cell B is blank then the cell A is "No" as well.

Answer 1

A combination of IF, OR, ISBLANK and UPPER should solve your problem.

=IF(UPPER(B2)="LOW","Yes",IF(OR(UPPER(C2)="PLEASE FILL IN",ISBLANK(C2)),"Yes","No"))

Answer 2

Im not great with formulas but this would do

=IF(LEN(B1)>0,IF(B1="low","Yes",IF(B1="medium",IF(C1<>"please fill in",IF(C1<>"","Yes","No"),"No"),IF(B1="high",IF(B1<>"please fill in",IF(LEN(C1)>0,"Yes","No"),"No"),"No"))),"No")

if you want a VBA function ( but requires you to enter the formula in each cell separately then stick this in a module and then use it in column A

Function CheckIt() As String
    Dim r As Long
    r = ActiveCell.Row
    If Range("B" & r) = "low" Then
        CheckIt = "Yes"
    Else
        If (Range("B" & r) = "medium" Or Range("B" & r) = "high") Then
            If ((Range("C" & r) <> "please fill in") And (Not IsEmpty(Range("C" & r)))) Then
                CheckIt = "Yes"
            Else
                CheckIt = "No"
            End If
        Else
            CheckIt = "No"
        End If
    End If
End Function

Example

Answer 3

The following formula replicates textually the logic you asked about

=IF(B1="Low","Yes",IF(OR(B1="Medium",B1="High"),IF(OR(ISBLANK(C1),C1<>"please fill in"),"Yes","No"),IF(ISBLANK(B1),"No","Undefined")))

Note that there is a case that you left out of your definition (which gives "Undefined"). This is probably not intended.

If "Undefined" is actually not intended, note also that you have only two possible outputs, so the condition can be replaced by a different test, with a single condition combining AND/ORs instead of nested IFs.