Dynamic IF AND statement

[GerdS55]

Hi,

I am not sure if this is even possible to do, but here goes.  The spreadsheet has stock prices over a time period.  There are dozens of rows of stocks that I will need to copy this same set of formulas to.

1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2
3			Rows	0
4			Columns	3
5
6
7
8
9					2023-09-22	2023-09-21	2023-09-20	2023-09-19	2023-09-18	2023-09-15	2023-09-14	2023-09-13	2023-09-12	2023-09-11	2023-09-08	2023-09-07	2023-09-06	2023-09-05	2023-09-01	2023-08-31	2023-08-30	2023-08-29	2023-08-28	2023-08-25	2023-08-24	2023-08-23	2023-08-22	2023-08-21	2023-08-18
10					$    111.41	$    109.73	$    112.47	$    112.31	$    112.69	$    115.91	$    114.88	$    113.43	$    112.78	$    113.10	$    113.99	$    116.26	$    117.94	$    118.73	$    121.91	$    121.07	$    122.68	$    122.02	$    119.92	$    119.68	$    119.49	$    119.13	$    117.64	$    118.86	$    119.25
11					$     111.20	$     111.50	$     112.49	$     113.64	$     114.49	$     114.74	$     113.70	$     113.10	$     113.29	$     114.45	$     116.06	$     117.64	$     119.53	$     120.57	$     121.89	$     121.92	$     121.54	$     120.54	$     119.70	$     119.43	$     118.75	$     118.54	$     118.58	$     119.06	$     119.25
12																				1	1	1	1
13
14

Row 10 has a range of values

Cell E11 has the formula: =average(E10:index(E10:AD10,$D$3,$D$4)) so it averages the values from E10:G10.  If I change Cell D4 to 6 then it would average the values from E10:J10.  The formula in cell E11 is copied to all the cells to the right.

In cell E12 I have the following formula:  =if(and(E11>F11,F11>G11,G11>H11),1,"")  So there are three AND conditions.  What I would like to do is re-write the formula so that the AND conditions would be the same as the value in Cell D4.  So as in the above example (changing Cell D4 to 6) , I would like the formula to be equivalent of: =if(and(E11>F11,F11>G11,G11>H11,H11>I11,I11>J11,J11>K11),1,"")

I hope all this makes sense.  Any help would be greatly appreciated.

[#2]

@GerdS55 

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.

[#3 · Accepted solution]

@GerdS55 

=IF(AND(OFFSET(E11,0,0,1,$D$4)>OFFSET(E11,0,1,1,$D$4)),1,"")

[#4]

Thank you very much. This works perfectly for what I am trying to do.

[#5]

Thank you for your reply. I am not sure how this works and it is not giving me the results I am looking for. I have found a solution from the next posters answer.