constructing an nested if excel formula

Question

I have a scenario where I need to identify a result. Below is a sample excel cells with three columns Prod Name, Qty and Result.

Prod Name    Qty     Result
abc          10 
zyz          9  
test1        5

If the product name is abc or zyz and its qty is 10, then I need to add text Refill in Column Result.

For any other product. in this case test1 and its qty is 5, then I need to add the same text Refill in Column Result.

Else it will always be Don't Refill

What formula have you tried? What problem have you encountered? As it is, you have given a specification but haven't actually asked a question. — John Coleman
– John Coleman, Commented Jun 1, 2018 at 13:28

Tim Biegeleisen · Accepted Answer · 2018-06-01 13:34:33Z

4

Try this formula:

=IF(OR(AND(OR(A1="abc", A1="xyz"), B1=10), AND(AND(A1<>"abc", A1<>"xyz"), B1=5)),
    "Refill",
    "Don't Refill")

edited Jun 1, 2018 at 13:34

answered Jun 1, 2018 at 13:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Craner Over a year ago

For any other product. in this case test1 I think that there are more than just that one case for 5

Tim Biegeleisen Over a year ago

@ScottCraner Good catch. You've been staring into spreadsheets for way too long :-)

Scott Craner Over a year ago

I find it relaxing after spending too much time in sql databases all day. Excel is a little simpler.

Scott Craner Over a year ago

Just an FYI: OR(A1="abc", A1="xyz") can be shortened to OR(A2={"abc","zyz"}) and AND(AND(A1<>"abc", A1<>"xyz"), B1=5) to AND(A2<>{"abc","zyz"},B2=5) without the need of CSE.

Samuel Hulla · Accepted Answer · 2018-06-01 14:05:38Z

0

The formula provided by Tim above should work nicely, but if you're looking for VBA approach:

(Presuming the data is in a table called Table1 in a sheet called Sheet1)

Private Sub comb_products()

  Dim tbl As ListObject: Set tbl = Sheets("Sheet1").ListObjects("Table1")

For Each Rng In tbl.ListColumns(1).DataBodyRange


 If ((Rng.Value2 = "abc" Or Rng.Value2 = "zyz") And Rng.Offset(0, 1).Value2 = 10) Then
        Rng.Offset(0, 2).Value2 = "Refill"
 ElseIf ((Rng.Value2 <> "abc" Or Rng.Value2 <> "zyz") And Rng.Offset(0, 1).Value2 = 5) Then
        Rng.Offset(0, 2).Value2 = "Refill"
 Else
        Rng.Offset(0, 2).Value2 = "Don't refill"
 End If

Next Rng

End Sub

Result as expected:

edited Jun 1, 2018 at 14:05

answered Jun 1, 2018 at 13:59

Samuel Hulla

7,2808 gold badges46 silver badges82 bronze badges

Collectives™ on Stack Overflow

constructing an nested if excel formula

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related