Forum Discussion
Dynamic IF AND statement
=IF(AND(OFFSET(E11,0,0,1,$D$4)>OFFSET(E11,0,1,1,$D$4)),1,"")
You can achieve this dynamic AND condition based on the value in Cell D4 by using an array formula with the AND and INDEX functions.
Here is how you can modify your formula to achieve this:
In Cell E12, you can use the following array formula:
=IF(AND(E11>F11:F11+ROW(F11:F11)-ROW(F11), ROW(F11:F11)-ROW(F11)<=D4), 1, "")
To enter an array formula, you need to press Ctrl + Shift + Enter instead of just Enter after typing the formula. This will tell Excel to treat it as an array formula. If entered correctly, Excel will automatically place curly braces {} around the formula.
Here is how this formula works:
- E11 is compared to a range of values from F11 to F11 + ROW(F11:F11) - ROW(F11). The ROW function is used to create an array of numbers based on the position of each cell relative to F11.
- ROW(F11:F11) - ROW(F11) calculates the relative row position within the range.
- D4 is used to limit the number of conditions in the AND function based on the number of columns you want to consider.
So, if D4 is set to 6, the formula will evaluate E11 > F11:F16 and F11:F16 > G11:H16 (up to 6 columns) and return 1 if all conditions are met.
Remember to adjust D4 to the desired number of columns you want to include in the AND condition, and enter the formula as an array formula as described above. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.
