Using ArrayFormula with Spreadsheet Attached to Google Form Does Not Always Yield Correct Result

[tthiele]

I have a spreadsheet that needs to do X, Y, or Z (which are all various sums of columns of numbers) depending upon two variables:

If A=5 and B<>0, then do X.
If A=6 and B=0, then do Y.
Else, do Z.

I have a formula that works correctly without using ArrayFormula:

=int(if(and(E2="5 courses each semester",J2<>"No Foreign Language Class Needed"),(sum(R2:V2))/2,if(and(E2<>"5 courses each semester",J2="No Foreign Language Class Needed"),(sum(R2:X2))/2,(sum(R2:W2))/2)))

However, because I am using this spreadsheet as attached to a Google Form, I wish to be able to use ArrayFormula with this so that the calculation can be part of an automated email response ("You can expect ** minutes of homework."). The formula I use for some reason flip-flops doing X and Y as listed above. Z works correctly. The formula used creating these incorrect results is:

=
ArrayFormula(int(if(and(E2:E="5 courses each semester",J2<>"No Foreign Language Class Needed"),AC2:AC,if(and(E2:E="6 courses each semester",J2="No Foreign Language Class Needed"),AD2:AD,AE2:AE))))

The columns AC, AD, and AE are array formulas used to correspond to the associated sums in the non-arrayformula above:

AC: =ArrayFormula((R2:R+S2:S+T2:T+U2:U+V2:V)/2)
AD: =ArrayFormula((R2:R+S2:S+T2:T+U2:U+V2:V+W2:W+X2:X)/2)
AE: =ArrayFormula((R2:R+S2:S+T2:T+U2:U+V2:V+W2:W)/2)

I can completely work around this problem by taking the functioning-properly, non arrayformula and dragging it after each response and then pushing the auto-generated email response to students, but it just seems like the arrayformula should work. The X, Y, and Z sums are calculating correctly, I just cannot determine why the nested if statements are not pulling the correct values. I would appreciate any assistance.