Google-sheets – ARRAYFORMULA and COUNTIF

google sheets

I have a formula:

=IFERROR(COUNTIF(C14:V14,"I")/ COUNTIF(C14:V14,"<>"),)

to compute a percentage of I inputs divided by <> (all other inputs) and I need to omit blank cells. The IFERROR formula just keeps the cell blank until data is entered in the corresponding row.

I want this formula to extend to every new row of a column. I have used ARRAYFORMULA before to automatically extend the formula to new rows but I can't figure out how to use it (or something else?) in this case.

Best Answer

assuming your data starts in row 14, try:

=ArrayFormula(IF(LEN(C14:C),COUNTIF(IF(C14:O="I",ROW(C14:C)),ROW(C14:C))/COUNTIF(IF(C14:O<>"",ROW(C14:C)),ROW(C14:C)),))

and format the column containing (the output of) this formula as % (via the 123-button).

Related Solutions

Google-sheets – Return a blank when cell reference is blank

Google Sheets as well as other spreadsheet application handles dates as serialized number. The large number that you refer that is displayed when a cell on AL column is blank is very likely that it is the number that represents the date on the corresponding cell on column G.

Regarding returning a blank, try something like this:

=ArrayFormula(IF(ISBLANK(A:A),,"Non blank"))

Please note that the second argument of the IF() function is left empty (,,): IFS(), IF() and other similar functions return blank when the corresponding argument is empty.

Google Sheets – Automatically Fill Missing Values

I added this formula to your spreadsheet

=ArrayFormula(if(row(B2:B) <= max(if(not(isblank(B2:B)), row(B2:B))),vlookup(row(B2:B),filter({row(B2:B),B2:B},len(B2:B)),2),))

It seems to deliver what you asked, except for the interpolation part. Enter this formula in row 2, no need to drag down. I hope this helps ?

Best Answer

Related Solutions

Google-sheets – Return a blank when cell reference is blank

Google Sheets – Automatically Fill Missing Values

Related Topic