macro needed for non-blank cells

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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!

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

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

 

[Guest]

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

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!

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

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

:

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


:

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!