Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulas

Formula to replace 0 with empty cell where F:F used range end

Using below formula that is working perfectly but it is mentioning extra 0.0 which i do not want.

I just want that where F:F range ends this formula should end too it should work with F:F range.

for Example: F5 has values, formula will give result accordingly otherwise it would be 0 if F6 is empty then formula will also be empty.

I have attached below picture please check.

=ArrayFormula(ROUND(IF(LEN(G3:G),IF(F3:F="Confirmed",(13 - MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))) * 13/12,IF(F3:F="Probation",0)))))

https://ibb.co/F38hd7Y

same with this it should work with col"E"

=ArrayFormula(IFERROR(1/(1/(IF(E3:E="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE({Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG,Dec!C3:AG}),,9^9))), " ", )))))))

Want to convert this formula into array

=IF(A2:A="","",COUNTIF(Jan!C3:AG3,"L")+COUNTIF(Jan!C3:AG3,"H")/2+COUNTIF(Jan!C3:AG3,"S")/4)
Solution

  • This should work for the first formula:

    =ArrayFormula(IF(LEN(F1:F),ROUND(IF(LEN(G3:G),IF(F3:F="Confirmed",(13 - MONTH(DATEVALUE(TEXT(G3:G,"mmm")&" 1"))) * 13/12,IF(F3:F="Probation",0)))),""))

    and this should work for the second one:

    =ArrayFormula(IF(LEN(E1:E),IFERROR(1/(1/(IF(E3:E="",,LEN(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE({Jan!C3:AG, Feb!C3:AG, Mar!C3:AG, Apr!C3:AG, May!C3:AG, Jun!C3:AG, Jul!C3:AG, Aug!C3:AG, Sep!C3:AG, Oct!C3:AG, Nov!C3:AG,Dec!C3:AG}),,9^9))), " ", )))))),"")