google-sheetsgoogle-sheets-formula

Why do I get the error SPLIT parameter 1 empty error when it isn't?


I'm using the formula below to divide numbers in the format (EX:2/4/6/8) from column E by the number in column D (EX: 2) and provide output in the same format from column E (1/2/3/4).

=ARRAYFORMULA(IF((ROW(D2:D) = 4) + (ROW(D2:D) = 9) + (D2:D = "") + (E2:E = "") + (D2:D = 0), "", TEXTJOIN("/", TRUE, SPLIT(E2:E, "/") / D2:D)))

The error I'm receiving is that in rows that have values and aren't being skipped by the IF (4 and 9), I'm receiving the error saying "Function SPLIT parameter 1 value should be non-empty." I don't understand how this could be the case since the formula should be skipping the rows where parameter 1 is empty in the first place, and the error occurs in rows where it shouldn't be empty. I have no experience with spreadsheets like this but understand the code and can't find anything that seems like it would cause the error so any help is greatly appreciate!


Solution

  • Although I don't completely understand why you are using ROW. I think the issue within your code is first, the if condition will always result in "FALSE" since you are using +, then your else (false) condition does not really divide each value of column E to D since your arrayformula does not cover the values within the split function.

    Now, if your goal is to split the values in column E, then divide each with the value of column D, then join them together. I have crafted a solution that you may try.

    Complete formula:

    =MAP(D2:D,E2:E, LAMBDA(x,y, IF(OR(x = 0, x = "", y = ""), "",JOIN("/", BYCOL(SPLIT(y, "/"), LAMBDA(c, TEXT(c/x, "0.00")))))))
    

    Sample result:

    Divided by values Answer result
    2 2/4/6/8 1.00/2.00/3.00/4.00
    3 3/6/9/12 1.00/2.00/3.00/4.00
    0 2/4/6/8
    2
    2 10/20/30 5.00/10.00/15.00
    0 5/10/15/20
    2 2/4/2006 1.00/2.00/1003.00
    2 2/4/6/8/10 1.00/2.00/3.00/4.00/5.00
    2 5/10/15 2.50/5.00/7.50
    3 12/15/18/21 4.00/5.00/6.00/7.00
    4 0/16/24/32 0.00/4.00/6.00/8.00

    References:

    1. BYCOL
    2. Map