CODEWITHSUNDEEP
excelexcel-formula
joehua
joehua

Reputation: 735

Is this an array formula, IF(ISNUMBER(A1:A100),A1:A100,"")

Is IF(ISNUMBER(A1:A100),A1:A100,"") an array formula? If not, what is it?

I copied a list of holidays from a web site and pasted it to Excel. The original data had four columns (date, name of the holiday, weekday, note). After pasting to Excel, everything appeared in column A, like this:

date
name of the holiday
weekday
note

date
name of the holiday
.....
.....

I tried several ways to see if the data could be pasted into columns to no avail. So, I needed to extract the dates to another column. In column B, I entered this formula =IF(ISNUMBER(A1:A100),A1:A100,""). It worked to extract the dates from column A to column B.

I am not sure if it is an array formula as, unlike other array formulae, it doesn't need Ctrl-Shift-Enter. Yet, ISNUMBER usually takes a cell as the argument, not an array, and IF usually doesn't return an array.

Upvotes: 1

Views: 3383

Answers (2)

Michael
Michael

Reputation: 4848

IF and ISNUMBER are not array functions, but they can be used in array formulas.

Outside of an array formula, they accept only single input values and return single output values.

In an array formula, they can accept an array of inputs values and will return an array of outputs values.

Without pressing CTRL-SHIFT-ENTER, the formula you've specified is not an array function.

What you're seeing is the behaviour when a function that is expecting a single value is given a range and entered without pressing CTRL-SHIFT-ENTER but the input range overlaps with the row where the formula is placed.

Entering a range when a single value is expected will return the value from the range on the same row in which the formula is entered if it overlaps, otherwise it will return an error.

You are getting output only because you are placing the formula next to the rows being referenced; and it is giving the same result as if you had entered the formula normally and then filled down: i.e. putting =IF(ISNUMBER(A1),A1,"") in cell B1 and filling down.

Note that if you entered =IF(ISNUMBER(A1:A100),A1:A100,"") in cell B2 instead of cell B1 and then filled down, then the values still appear in the same row and not shifted down by a row as you might expect. Again, this is because it looks at the value in the same row just because the range overlaps with the current row.

Compare to putting =IF(ISNUMBER(A1),A1,"") in cell B2 and filling down, where you then get the values shifted down by a row as expected.

If you entered the original formula again in cell B101 (below the input range) and filled down, you get no values at all (even if there is data next to the cell) because the input range no longer overlaps the current cell. In fact, if you used the Evaluate Formula tool from the Formulas tab, you'll see that the range A1:A100 returns a #VALUE error immediately.

This behaviour is confusing and should be avoided at all costs. Only enter ranges when a function expects a range, or when an array formula is being intentionally created using CTRL-SHIFT-ENTER.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 36890

If after pasting your data to excel sheet looks like below then you can use following formula.

enter image description here

As shown to above screenshot user below formula to C1 cell then drag down and right as needed.

=INDIRECT("A"&(ROW()-1)*4+COLUMNS($A$1:A$1))

This formula will produce 0 (zero) for empty cells. To hide zero (0) use a IF() condition like below.

=IF(INDIRECT("A"&(ROW()-1)*4+COLUMNS($A$1:A$1))=0,"",INDIRECT("A"&(ROW()-1)*4+COLUMNS($A$1:A$1)))

Upvotes: 0

Related Questions