Re: conversion of variables to constants

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi driller,

The 1st formula tests the column for *numbers* only, including numbers
generated by formulae. If your column has both numbers and text strings,
then only the last row with a *number* will be returned.
The 2nd formula tests the column for *text strings* only, including text
strings generated by formulae. If your column has both numbers and text
strings, then only the last row with a *text string* will be returned.
The 3rd formula tests the column for *numbers and text strings*, including
numbers and text strings generated by formulae. If your column has both
numbers and text strings, then the last row with either a *number or a text
string* will be returned.

I should have mentioned that the 2nd and 3rd versions may fail if there are
cells with nul values in the range.

Cheers
--
macropod
[MVP - Microsoft Word]


"driller" <driller@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8025D81A-5F85-47EA-AA06-AEFB7BD72BCC@xxxxxxxxxxxxxxxx
excuse me beecher,
Hi macropod, i test the 3 formulas
the first one spots the last number perfectly
the second and third one do not looks for the right spot.

i test a column Y with data per cell (i.e. 1,2,3,2x,6,8,9,3x,5). it seems
my
excel is not responding to spot the last text 3x.
please explain ?

"macropod" wrote:

Hi beecher,

To find the row with the last numeric value in column Y, you could use:
=MATCH(1E+306,Y:Y,1)
To find the row with the last text value in column Y, you could use:
=MATCH("*",Y:Y,-1)
To find the row with the last alphanumeric value in column Y, you could
use:

=MAX(IF(ISERROR(MATCH("*",Y:Y,-1)),0,MATCH("*",Y:Y,-1)),IF(ISERROR(MATCH(1E+
306,Y:Y,1)),0,MATCH(1E+306,Y:Y,1)))

Cheers

--
macropod
[MVP - Microsoft Word]


"beecher" <beecher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C8AEE594-869C-4EF1-87B1-E8B0C6CFE171@xxxxxxxxxxxxxxxx
I am multiple worksheets and am running into a problem. The formula I
have
utilizes constants (i.e. $Y$4) to demarcate the last cell in a column.
For
instance, if the column expanded from Y1-Y100 I would use $Y$100 in my
formula for each cell in another column (i.e. column Z). However,
each of
my
worksheets has a different number of rows such that the column in
question
has varying length. Sometimes the column stretches from Y1-Y40, other
times
from Y1-Y2000. Is there a quick way I can convert the last cell in
each
column into a constant. This may help:

I have I would like
Y1-Y100 $Y$100
Y1-Y2000 $Y$2000
Y1-Y53 $Y$53

Thanks a lot,
beecher





.

Quantcast