I need ARRAYFORMULA() to use conditions in another row to decide what to calculate-CodePudding

Here is my formula in G5 =ARRAYFORMULA(IF(AND(J5:J17=true,H5:H17=false),E5:E17*F5:F17*1.075,IF(AND(J5:J17=true,H5:H17=TRUE),0,IF(and(J5:J17=false,H5:H17=false),E5:E17*F5:F17,IF(and(J5:J17=false,H5:H17=true),0)))))

This works if Cols H,I, and J are all false, however once I start setting anything in H,I,or J to true, the formula stops working.

Thanks for any help, Im really stumped on this one.

CodePudding user response：

I think you can simplify your formula all the way down to:

=arrayformula(E5:E17*F5:F17*not(H5:H17)*if(J5:J17,1.075,1))

CodePudding user response：

use:

 =ARRAYFORMULA(IF((J5:J17=true)*(H5:H17=false), E5:E17*F5:F17*1.075,
  IF((J5:J17=true)*(H5:H17=TRUE), 0,
  IF((J5:J17=false)*(H5:H17=false), E5:E17*F5:F17,
  IF((J5:J17=false)*(H5:H17=true), 0)))))

CodePudding user response：

You can try to do this with MAP:

=MAP(J5:J17,H5:H17,E5:E17,F5:F17,LAMBDA(j,h,e,f,
IF(AND(j=true,h=false),e*f*1.075,IF(AND(j=true,h=TRUE),0,IF(and(j=false,h=false),e*f,IF(and(j=false,h=true),0))))))