-1

I have a large sheet with around 4000 rows of data, and a config on another sheet. It all seems to work fine when the functions are in each cell, but once i tried turning it to an ArrayFormula, it just gives a blank cell. It didn't return an error, just blank. Here is the formula when its in its individual cell.

=IF(AND(ISNUMBER($N3),ISNUMBER($O3),ISTEXT($A3),ISTEXT($B3),$F3="A"),IF((($N3*Config!D$3*(1+VLOOKUP(($A4&"|"&$B4),Config!$C$16:$J,2,FALSE))*(1+Config!D$8)+((Config!D$6+(Config!D$7*$O3))*Config!D$11))/(1-Config!D$5)+(20*Config!D$3*(1+(Config!D$5+Config!D$8))))>(Config!D$10*Config!D$11),(($N3*Config!D$3*(1+VLOOKUP(($A4&"|"&$B4),Config!$C$16:$J,2,FALSE))*(1+Config!D$8)+((Config!D$6+(Config!D$7*$O3))*Config!D$11))/(1-Config!D$5)+(20*Config!D$3*(1+(Config!D$5+Config!D$8)))),($N3*Config!D$3*(1+VLOOKUP(($A4&"|"&$B4),Config!$C$16:$J,2,FALSE))*(1+Config!D$9)+((Config!D$6+(Config!D$7*$O3))*Config!D$11))/(1-Config!D$5)+(20*Config!D$3*(1+(Config!D$5+Config!D$9)))),"")

However when I tried changing it to Array formula, it just does nothing.

=IF(AND(ISNUMBER($N3:$N),ISNUMBER($O3:$O),ISTEXT($A3:$A),ISTEXT($B3:B),$F3:$F="A"),IF((($N3:$N*Config!D$3*(1+VLOOKUP(($A4:$A&"|"&$B4:$B),Config!$C$16:$J,2,FALSE))*(1+Config!D$8)+((Config!D$6+(Config!D$7*$O3))*Config!D$11))/(1-Config!D$5)+(20*Config!D$3*(1+(Config!D$5+Config!D$8))))>(Config!D$10*Config!D$11),(($N3:$N*Config!D$3*(1+VLOOKUP(($A4:$A&"|"&$B4:$B),Config!$C$16:$J,2,FALSE))*(1+Config!D$8)+((Config!D$6+(Config!D$7*$O3:$O))*Config!D$11))/(1-Config!D$5)+(20*Config!D$3*(1+(Config!D$5+Config!D$8)))),($N3:$N*Config!D$3*(1+VLOOKUP(($A4:$A&"|"&$B4:$B),Config!$C$16:$J,2,FALSE))*(1+Config!D$9)+((Config!D$6+(Config!D$7*$O3:$O))*Config!D$11))/(1-Config!D$5)+(20*Config!D$3*(1+(Config!D$5+Config!D$9)))),"")

What the formula does is it calculates the sell price of items based on the Cost, the weight and the settings set in the "Config" sheet. The profit margin is pulled from "Config" as well with the VLookup formula.

The thing is I am trying to use ArrayFormula so whenever I add new items it will automatically calculate the sell price. I am planning in the end put this formula in the 2nd row, to calculate anything from row 3 and below, so that when I sort the rows, it does not move my formulas around.

At first I thought it was the vlookup issue, but when I tried it separately with an arrayformula it seems to run just fine. I am starting to wonder if it is the ISNUMBER and ISTEXT that is checking all the rows instead of just one by one, but I don't know any other way to do it, or how to check if this is the issue.

EDIT: Added a duplicate of the file which allows editing and viewing as well Here is the google sheet doc which can be edited

The formula is in the "Product" sheet in cell G3, and the data pulled is in "Config" sheet.
The Vlookup formula basically pulls data with 2 criterias, column A and B, but attaches them together so it can be matched to cells in the C16:C in "config"sheet.

Any help is greatly appreciated. Thanks

  • 1
    Welcome. Please add a link to a demo spreadsheet shared with anyone with the link to view only. Also describe what your formula does and what where the changes that you made to convert it into an array formula as well a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Mar 24 at 14:33
  • 1
    Oh yes, I added a duplicate of the file and shared it, and can be edited as well since it doesn't change my original file. – Cynric Ng Mar 24 at 14:41
  • Since you added the demo spreadsheet with edit access please add a description of the spreadsheet state before it be edited including sample data of the referred cells/ranges. – Rubén Mar 24 at 14:48
  • I tried out a smaller formula of =ARRAYFORMULA(ISTEXT(B2:B)) this seems to work, and =ARRAYFORMULA(ISNUMBER(B2:B)) seems to work fine too. However =ARRAYFORMULA(AND(ISNUMBER(A2:A),ISNUMBER(B2:B))) doesn't seem to work. I think the AND is the issue which is not working with arrayformula, however I don't know another way to get around this. – Cynric Ng Mar 24 at 22:18
  • I found the solution to the formula. For anyone interested, it was due to the fact that Arrayformula does not work with certain logic expressions such as AND, OR, NOT etc. There instead of IF(AND(ISNUMBER($N3:$N),ISNUMBER($O3:$O),ISTEXT($A3:$A),ISTEXT($B3:B),$F3:$F="A")) I had to replace it with IF((ISNUMBER($N3:$N))*(ISNUMBER($O3:$O))*(ISTEXT($A3:$A))*(ISTEXT($B3:B))*($F3:$F="A") – Cynric Ng Mar 24 at 22:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.