Embedded Files
You may have found that AND() and OR() do not work with Dynamic Arrays (DAs). The workaround is to use math. This leverages a quirk in Excel where TRUE equates to 1 and FALSE equates to 0.
To illustrate the problem and the workaround, we will use the example below. On the left is a table (tblItems). To the right of that table are two DAs.
The Dynamic Arrays
The first DA is a single row containing date headers named "Date". The second DA is the matrix below Date. We want this DA to post amounts under the appropriate dates and for the appropriate item.
The Problem
For the matrix, we might think this would work:
=If(AND(tblItems[Start]<=Date, tblItems[End]>=Date), tblItems[Estimate], 0)
It does not because AND() ands the entire matrix, not just the row.
Replacing AND()
The work around is to multiply the comparisons which negates the need for IF()
=(tblItems[Start]<=Date)*(tblItems[End]>=Date)*tblItems[Estimate]
Replacing OR()
In our example; however, our client does not want to enter End dates for those items that have no end in sight. Thus, we want to compare tblItems[End] to the Date or 0. To accommodate this OR() situation we use addition.
=(tblItems[Start]<=Date) * ((tblItems[End]>=Date)+(tblItems[End]=0)) * tblItems[Estimate]
OR() when multiple comparisons could be TRUE
The above works when the two OR() conditions cannot both be TRUE. When both could be TRUE we can use ROUNDUP() and division by the number of comparisons:
=(tblItems[Start]<=Date) * ROUNDUP(((tblItems[End]>=Date)+(tblItems[End]=0))/2,0) * tblItems[Estimate]
Page updated
Google Sites
Report abuse