Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-conditional-formatting

use ISFORMULA with ARRAYFORMULA on a range of cells

I have a row of cells, some with a formula, and some without.

I am trying to use ARRAYFORMULA WITH IFFORMULA to see if a cell has a formula or not. This is the function I am using:

=ARRAYFORMULA(ISFORMULA(B2:B))

But it just outputs one single value.

enter image description here

There are other things I need to do for each row/cell which is why I need ARRAYFORMULA.

Is there another way to get ISFORMULA to work with ARRAYFORMULA?

Solution

  • not all functions are convertible into ArrayFormula. ISFORMULA is one of those...

    the best you can do is pre-program it like:

    ={ISFORMULA(B2);
  ISFORMULA(B3);
  ISFORMULA(B4);
  ISFORMULA(B5);
  ISFORMULA(B6)}

    0

    the next best thing you can do is to use conditional formatting to color it like:

    green color:

    =ISFORMULA($B2)

    red color:

    =NOT(ISFORMULA($B2))*(B2<>"")

    0

    UPDATE

    now possible with lambda:

    =BYROW(B2:B6; LAMBDA(x; ISFORMULA(x)))