macro needed for non-blank cells
 

I am trying to write a formula that is contingent on an adjacent column.
Specifically, I am writing a formula using multiple IF worksheet functions.
In an adjacent column I have a series of 1's and 0's with non-blank cells in
between. Does anyone know how to write a formula that will read a previous
column for the last non-blank cell?

Here is an illustration that might help:

Column 1 Column 2
0
IF function to be put here that will give a 'true' value only
if the last non-
blank cell in column 1 is equal to 0
0


1


0


1

Thanks a lot!

macro needed for non-blank cells
 

Try something like this:

With
Cells A1:A100 containing 1's, 0's, or other values

This formula tests if the last non-blank cell in A1:A100 equals zero
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

I am trying to write a formula that is contingent on an adjacent column.
Specifically, I am writing a formula using multiple IF worksheet functions.
In an adjacent column I have a series of 1's and 0's with non-blank cells in
between. Does anyone know how to write a formula that will read a previous
column for the last non-blank cell?

Here is an illustration that might help:

Column 1 Column 2
0
IF function to be put here that will give a 'true' value only
if the last non-
blank cell in column 1 is equal to 0
0


1


0


1

Thanks a lot!

macro needed for non-blank cells
 

Hey Ron,

Thanks for the help. I was trying to put the formula you suggested into my
spreadsheet but excel responds that I had made an error when typing it in.

Would you be able to explain to me what the 2,1 in the formula means?

Thanks

"Ron Coderre" wrote:

Try something like this:

With
Cells A1:A100 containing 1's, 0's, or other values

This formula tests if the last non-blank cell in A1:A100 equals zero
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

I am trying to write a formula that is contingent on an adjacent column.
Specifically, I am writing a formula using multiple IF worksheet functions.
In an adjacent column I have a series of 1's and 0's with non-blank cells in
between. Does anyone know how to write a formula that will read a previous
column for the last non-blank cell?

Here is an illustration that might help:

Column 1 Column 2
0
IF function to be put here that will give a 'true' value only
if the last non-
blank cell in column 1 is equal to 0
0


1


0


1

Thanks a lot!

macro needed for non-blank cells
 

Would you be able to explain to me what the 2,1 in the formula means?

Sure thing...This will be a bit wordy (sorry).

Regarding
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

In this case, LOOKUP has 3 arguments (parameters)
LOOKUP([Find this], [in this list], [Return the corrsponding value from this
list])
So we have....
Find this: 2
In this list: 1/(A1:A100<"")
and
Return the corrsponding value from this list: A1:A100

Let's start with the second argument: 1/(A1:A100<"")
(A1:A100<"") tests each item in A1:A100 to see if it is blank.
NonBlanks return 1
Blanks return 0
(Actually TRUE's and FALSE's, respectively, but because we're using math,
Excel converts them to 1's and 0's)

When 1 is divided by those values in: 1/(A1:A100<""), the argument becomes
an array of 1's (for NonBlanks) and #DIV/0! errors (for Blanks).

OK...back to the LOOKUP.
The array only returns 1's and errors. The LOOKUP function ignores the
errors and scans the numbers. So, we are effectively looking for a 2 in array
of 1's.

LOOKUP has a nice and predictable and somewhat non-intuitive behavior when
the lookup value is larger than the largest array value.....it simply matches
on the last value! In this case, that is the last non-blank value in the
list. Since the return value array (the third argument) is the same as the
lookup array, the last non-blank value is returned.

In the complete formula we simply test if that value is zero.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

Hey Ron,

Thanks for the help. I was trying to put the formula you suggested into my
spreadsheet but excel responds that I had made an error when typing it in.

Would you be able to explain to me what the 2,1 in the formula means?

Thanks

"Ron Coderre" wrote:

Try something like this:

With
Cells A1:A100 containing 1's, 0's, or other values

This formula tests if the last non-blank cell in A1:A100 equals zero
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

I am trying to write a formula that is contingent on an adjacent column.
Specifically, I am writing a formula using multiple IF worksheet functions.
In an adjacent column I have a series of 1's and 0's with non-blank cells in
between. Does anyone know how to write a formula that will read a previous
column for the last non-blank cell?

Here is an illustration that might help:

Column 1 Column 2
0
IF function to be put here that will give a 'true' value only
if the last non-
blank cell in column 1 is equal to 0
0


1


0


1

Thanks a lot!

macro needed for non-blank cells
 

Hey Ron,

This equation now makes a lot more sense to me, thanks for the explanation.
I am still having some problems though when I type this into my spreadsheet.
When entered into my spreadsheet, the value given is #N/A for all cells
regardless of whether the last non-blank cell in the adjacent column is equal
to 1 or 0. Would you happen to have any recommendations on how to fix this
problem?

Thanks!

"Ron Coderre" wrote:

Would you be able to explain to me what the 2,1 in the formula means?

Sure thing...This will be a bit wordy (sorry).

Regarding
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

In this case, LOOKUP has 3 arguments (parameters)
LOOKUP([Find this], [in this list], [Return the corrsponding value from this
list])
So we have....
Find this: 2
In this list: 1/(A1:A100<"")
and
Return the corrsponding value from this list: A1:A100

Let's start with the second argument: 1/(A1:A100<"")
(A1:A100<"") tests each item in A1:A100 to see if it is blank.
NonBlanks return 1
Blanks return 0
(Actually TRUE's and FALSE's, respectively, but because we're using math,
Excel converts them to 1's and 0's)

When 1 is divided by those values in: 1/(A1:A100<""), the argument becomes
an array of 1's (for NonBlanks) and #DIV/0! errors (for Blanks).

OK...back to the LOOKUP.
The array only returns 1's and errors. The LOOKUP function ignores the
errors and scans the numbers. So, we are effectively looking for a 2 in array
of 1's.

LOOKUP has a nice and predictable and somewhat non-intuitive behavior when
the lookup value is larger than the largest array value.....it simply matches
on the last value! In this case, that is the last non-blank value in the
list. Since the return value array (the third argument) is the same as the
lookup array, the last non-blank value is returned.

In the complete formula we simply test if that value is zero.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

Hey Ron,

Thanks for the help. I was trying to put the formula you suggested into my
spreadsheet but excel responds that I had made an error when typing it in.

Would you be able to explain to me what the 2,1 in the formula means?

Thanks

"Ron Coderre" wrote:

Try something like this:

With
Cells A1:A100 containing 1's, 0's, or other values

This formula tests if the last non-blank cell in A1:A100 equals zero
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

I am trying to write a formula that is contingent on an adjacent column.
Specifically, I am writing a formula using multiple IF worksheet functions.
In an adjacent column I have a series of 1's and 0's with non-blank cells in
between. Does anyone know how to write a formula that will read a previous
column for the last non-blank cell?

Here is an illustration that might help:

Column 1 Column 2
0
IF function to be put here that will give a 'true' value only
if the last non-
blank cell in column 1 is equal to 0
0


1


0


1

Thanks a lot!

macro needed for non-blank cells
 

Let's start by verifying that you can get the basic formula to work:

Open a new workbook and put this formula in cell B1

=LOOKUP(2,1/(A1:A100<""),A1:A100)

It should initially return #N/A because there are no values in A1:A100

Next, enter all kinds of values in A1:A100
text, numbers, errors (eg =1/0), ...whatever

If the formula always returns the last non-error value listed, then it is
working properly. Consequently, there's some kind of anomaly in your "real"
worksheet. Post back with the results of the above test.

***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

Hey Ron,

This equation now makes a lot more sense to me, thanks for the explanation.
I am still having some problems though when I type this into my spreadsheet.
When entered into my spreadsheet, the value given is #N/A for all cells
regardless of whether the last non-blank cell in the adjacent column is equal
to 1 or 0. Would you happen to have any recommendations on how to fix this
problem?

Thanks!

"Ron Coderre" wrote:

Would you be able to explain to me what the 2,1 in the formula means?

Sure thing...This will be a bit wordy (sorry).

Regarding
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

In this case, LOOKUP has 3 arguments (parameters)
LOOKUP([Find this], [in this list], [Return the corrsponding value from this
list])
So we have....
Find this: 2
In this list: 1/(A1:A100<"")
and
Return the corrsponding value from this list: A1:A100

Let's start with the second argument: 1/(A1:A100<"")
(A1:A100<"") tests each item in A1:A100 to see if it is blank.
NonBlanks return 1
Blanks return 0
(Actually TRUE's and FALSE's, respectively, but because we're using math,
Excel converts them to 1's and 0's)

When 1 is divided by those values in: 1/(A1:A100<""), the argument becomes
an array of 1's (for NonBlanks) and #DIV/0! errors (for Blanks).

OK...back to the LOOKUP.
The array only returns 1's and errors. The LOOKUP function ignores the
errors and scans the numbers. So, we are effectively looking for a 2 in array
of 1's.

LOOKUP has a nice and predictable and somewhat non-intuitive behavior when
the lookup value is larger than the largest array value.....it simply matches
on the last value! In this case, that is the last non-blank value in the
list. Since the return value array (the third argument) is the same as the
lookup array, the last non-blank value is returned.

In the complete formula we simply test if that value is zero.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

Hey Ron,

Thanks for the help. I was trying to put the formula you suggested into my
spreadsheet but excel responds that I had made an error when typing it in.

Would you be able to explain to me what the 2,1 in the formula means?

Thanks

"Ron Coderre" wrote:

Try something like this:

With
Cells A1:A100 containing 1's, 0's, or other values

This formula tests if the last non-blank cell in A1:A100 equals zero
B1: =LOOKUP(2,1/(A1:A100<""),A1:A100)=0

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"beecher" wrote:

I am trying to write a formula that is contingent on an adjacent column.
Specifically, I am writing a formula using multiple IF worksheet functions.
In an adjacent column I have a series of 1's and 0's with non-blank cells in
between. Does anyone know how to write a formula that will read a previous
column for the last non-blank cell?

Here is an illustration that might help:

Column 1 Column 2
0
IF function to be put here that will give a 'true' value only
if the last non-
blank cell in column 1 is equal to 0
0


1


0


1

Thanks a lot!