Google Sheets array formula help needed-CodePudding

I am new to Google Sheets and am running into some trouble trying to figure out who to create a formula using ARRAYFORMULA, IF, AND..

I have a sheet that is used to create an inventory needed list for our satellite facility. I have the following formula that works fine for individual cells but I am have difficulty getting a grasp on how to convert this into an ARRAYFORMULA. I am using ARRAYFORMULA in other cells but with this in having multiple IF and AND, I'm stumped. Any assistance would be greatly appreciated.

=if(Isblank(N2),If(And(L2>=1,O2=""),Roundup(L2/C2), If(And(L2>=1,O2="Pallet"),"Pallet",If(L2<1,Roundup(L2/C2)))),"BO")

CodePudding user response：

AND is not supported under ARRAYFORMULA. you need to use multiplication:

=ARRAYFORMULA(IF(ISBLANK(N2:N),
 IF((L2:L>=1)*(O2:O=""),       ROUNDUP(L2:L/C2:C), 
 IF((L2:L>=1)*(O2:O="Pallet"), "Pallet", 
 IF( L2:L< 1,                  ROUNDUP(L2:L/C2:C)))), "BO"))