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


Board Regular
Jun 17, 2002
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.
Last edited:

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...