Forum Discussion
nested if & all statement returning the wrong values
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.
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.
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.
- 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!