Strange behaviour of the COLUMN function!

[Mohammad Basem]

Greetings,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>

I am new to this forum and this is my first problem to address.

I have problem with the below formula. It is supposed to show the last column used in the sheet, i.e. last column with a value in it,<o></o>
<o></o>

[COLOR=navy]{=MAX(NOT(ISBLANK(A2:Z1000))*COLUMN(1:26))}[/COLOR]

but I am getting the #N/A error instead.
<o></o>
<o></o>

After tracking the error, I found that Excel, no matter what number range in the argument, returns values from {1–16384}—I tried COLUMN(1:1), COLUMN(2:3) with same results.<o></o>
<o></o>

As a matter of troubleshooting, I replaced the number range with reference range,<o></o>
<o></o>

[COLOR=navy]{=MAX(NOT(ISBLANK(A2:Z1000))*COLUMN(A:Z))}[/COLOR]

<o></o>
and it worked fine!<o></o>
<o></o>

I have the original formula’s sister<o></o>
<o></o>

[COLOR=navy]{=MAX(NOT(ISBLANK(A2:Z1000))*ROW(2:1000))}[/COLOR]

<o></o>

working just fine to return the last row used.<o></o>
<o></o>

I want to keep using the number range (1:26) rather than the reference range (A:Z) and seeking any advice on what is wrong with the original formula.

It is worth to mention that I haven't experienced similar problems using the COLUMN function with number arguments.
<o></o>

Thank you in advance.<o></o>
MBA

 

[Jerry Sullivan]

Hi MBA,

In this expression:
{=MAX(NOT(ISBLANK(A2:Z1000))*COLUMN(1:26))}

Excel interprets 1:26 as meaning Rows 1:26, just like it would in the formula
=SUM(1:26)

So COLUMN(1:26) returns {1-16384} which is the column number of every column in your worksheet.

If you need to use numbers to reference the Columns, you could probably accomplish this with INDIRECT or OFFSET.

 

[Mohammad Basem]

Thank you Jerry for your fast response!

You are right; Excel considers the whole number of columns in the sheet, but why? Actually, I did not understand why most of my formulas work fine with ROW() but not COLUMN()!

Is it a bug...?

You have advised to use INDIRECT(). Do you mean like the one below?

[COLOR=Navy][FONT=Courier New]{=MAX(NOT(ISBLANK(A2:Z1000))*COLUMN(INDIRECT("1:26")))}[/FONT][/COLOR]

It gives same results.

Could you please show me how to use INDIRECT() or OFFSET() to get the last used column.

Thank you in advance
MBA

 

[Jerry Sullivan]

You are right; Excel considers the whole number of columns in the sheet, but why? Actually, I did not understand why most of my formulas work fine with ROW() but not COLUMN()!

Is it a bug...?

This isn't a bug, it's just the way Excel interprets the expression 1:26.
Similarly, Rows(A:B) will try to return {1-1048576}.

(This returned an Overflow error for me, but I'm pretty sure it's trying to return an Array of over a Million items).

This thread has some examples of how to use INDIRECT+ADDRESS or OFFSET.
http://www.mrexcel.com/forum/showthread.php?t=598795

 

[Mohammad Basem]

You are right in that Excel has its own way in interpreting things!

ROW(A:B) will extend to the bottom of the sheet since you did not specify a limit for the number of rows. It is not like my case, where I specified the number of columns—1:26.

Though useful, the link you’ve provided is talking about known references, while my case is to locate the references.

Back to my case, I have checked further and found that the behaviour of ROW() and COLUMN() are not the same when providing numeric arguments.

For example, ROW(1:5) will select the range of rows 1–5 * all columns (make sense), while COLUMN(2:10) will select the range of all columns * rows 2–10 (why? Expected behaviour is to select columns 2–10 * all rows). As if the numeric arguments always refer to rows only despite the used function.

My conclusion after this tiny research is that COLUMN() don’t care much about the numeric values while we have to be so precise with ROW()! It might not be a bug but COLUMN() works fine with references only.

Finally, I’ve found a solution to my problem, al-hamdullah. Simply replace COLUMN(1:26) with TRANSPOSE(ROW(1:26)). It gives the expected result.

Thank you very much for your support!
MBA

 

[Jerry Sullivan]

Finally, I’ve found a solution to my problem, al-hamdullah. Simply replace COLUMN(1:26) with TRANSPOSE(ROW(1:26)). It gives the expected result.

Ah- a very nice solution!