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

tthiele

Board Regular
Joined
Jun 17, 2002
Messages
67
Office Version
  1. 365
Platform
  1. MacOS
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:

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top