My first use of dynamic ranges. I am trying to build a payment table that outputs a static value with a row of years and a column of months. The formula referencing a dynamic range =IF(AND(MONTH($F$3#)=$B21,YEAR($F$3#=C$11)),$D$3,0)result is 0 in the example below. The Payment Schedule column is a dynamic formula created with =EOMONTH(C3,SEQUENCE(B3,1,6,6)). I have tried creating a dynamic range from =MONTH(F3#) and =YEAR(F3#), and that gives me a month/year dynamic range, but using those instead of month in the formula also results in 0. I am expecting 112.00 in the 4 and 10 cells for each year. Thanks for the help.

CodePudding user response：

The AND() function is not a row-wise function. Meaning, when you provide the AND() function with an array of TRUEs and FALSEs, if only one of the values is FALSE, the function will return FALSE. In other words, suppose you have two arrays of 4 values each, such that the AND() function looks like this: AND({1,0,0,1},{1,1,1,0}) the function will return 0.

If I understand you correctly, in the case of the 2 example arrays above ({1,0,0,1} and {1,1,1,0}) you want the function to return 1. To do that you can simply multiply the two arrays to do a row-wise AND calculation and then use the OR() function. The multiplication is possible even if the values are actually TRUEs and FALSEs instead of 1s and 0s.

For your formula that would spell out like this: =IF(OR((MONTH($F$3#)=$B21)*(YEAR($F$3#)=C$11)),$D$3,0)