CODEWITHSUNDEEP
excelexcel-formula
lStoilov
lStoilov

Reputation: 1339

Logical formula in Excel - Help needed

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

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.

Upvotes: 1

Views: 639

Answers (3)

Robert Mearns
Robert Mearns

Reputation: 11986

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"))

Example of formula

Upvotes: 1

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

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.

Upvotes: 2

user2140173
user2140173

Reputation:

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

enter image description here

Upvotes: 1

Related Questions