SOLVED

nested if & all statement returning the wrong values

Copper Contributor

I am using windows 10 with an excel version MS office Home and Student 2016. When I use the "if" statement with an "AND" statement, it always results in 0 (when some of the TRUE results result in different numbers, it continues down the false if path, even though I want the if statements to stop after finding the true statement). The formulas are under En-Route Inventory where I am attempting to remove a # of Truck Quantity 7 days after receiving it. The two types of formulas I've tried are as follows:

 

=IF(AND(B21=1,B14=0),O20+$U$4,IF(AND(B21=1,B14=1),O20,IF(AND(B21=0,B14=1),O20-$U$4,O20)))

 

=IF((B22=1) * (B15=0),O21+$U$4,IF((B22=1) * (B15=1),O21,IF((B22=0) * (B15=1),O21-$U$4,O21)))

 

The logic I am attempting is as follows: If B21=1 (inventory shipped out today) and B14=1 (inventory was shipped 7 days ago), than I'd be 150 units added - 150 units making it to their destination.

 

Below is an image of my Excel sheet:

https://imgur.com/ZXLz4QG 

 

4 Replies

@ryanlwaltman In your example both B21 and B14 are zero. None of the IF/AND conditions in the formula are met. Hence you'll end up with the value of O20 (the cell above), being zero.

When you drag this formula down to O22, it will look at B22 and B15 (1 and 0). The first condition is met and it should return 0 (O21) +150 (U4) = 150.

Drag the formula down one more row (O23). Now, both values are 0 again, returning 150 from O22. That will continue up to B28, (both values tested are 0) always returning the value for the cell above it. When you come down to O29, both B29 and B22 are 1.  The second IF/AND is met and it will return the value from O28, thus again 150.

Didn't replicate your file to test it though. So I may have misinterpreted your formula. But if that's not what you had in mind, you'll need to revise the formula.

@Riny_van_Eekelen Yes, that is the correct interpretation and intention of the formula. However, once at O22, the cell does not become 150 but rather stays 0. When I evaluate the formula, this is what I see:

https://imgur.com/a/h09FiAC

The formula should end at step 9, returning a value of 150. However, it continues to the false section and then returns a 0 for some reason. I'm not sure why the formula is doing this.

Nevermind, the issue was that I had multiple cells referencing off of each other, including the formula I used above, creating a circular formula. The issue was not the formula but the multiple references. Thank you for the help!
best response confirmed by ryanlwaltman (Copper Contributor)
Solution

@ryanlwaltman Quickly replicated the relevant parts of your schedule and the formula from your original post returns 150 from O22 and down. See attached.

 

Edit: didn't notice your comment on circular references until I posted this message.

1 best response

Accepted Solutions
best response confirmed by ryanlwaltman (Copper Contributor)
Solution

@ryanlwaltman Quickly replicated the relevant parts of your schedule and the formula from your original post returns 150 from O22 and down. See attached.

 

Edit: didn't notice your comment on circular references until I posted this message.

View solution in original post