so now i see why.. its perfectly clear and understood, eh! <bg
--
regards,
driller

*****
- dive with Jonathan Seagull



"T. Valko" wrote:

Now, all that's left to do is to add some error checking and you'll be good
to go! <g

The topic of this thread was "looking" for *numbers*.

range<""

That could apply to both numbers and text. Chances are that there is no text
in the range but we don't know that for certain. So, to be on the safe side
(read: being robust) I would use:

ISNUMBER(range)

That eliminates any chance of accidentally returning a text value.

=INDEX(range,MIN(IF(ISNUMBER(range),COLUMN(range)-MIN(COLUMN(range))+1)))

Starting to look pretty good, eh? But what happens if there aren't any
numbers in the range?

The formula returns 0. That might be OK but it could also be confusing since
that could mean the first number found was a 0. If the formula is on a
different sheet or even in a different file we don't know what 0 means. So,
I would add a test to make sure there are in fact numbers in the range:

=IF(COUNT(range),INDEX(range,MIN(IF(ISNUMBER(range ),COLUMN(range)-MIN(COLUMN(range))+1))),"")

Now that's a robust formula. It's even robust against any error values that
might be in the range. About the only "improvement" we could make is to
write the formula so it isn't an array formula (if possible, which it is).
See my first reply in this thread.

--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
maybe to brighten up more of another simpler yet versatile way, extending
your suggestion about *column()* blended array formula in one..

we can say that a *range* is better used via *defined name* for a group of
cells in one row, (eg. from 6 to 260 cells in a single row)

so...it can be generic this way..without a count function...plus an extra
grasp for grabbing text <USD,Eur,etc which represents prices <g

array entered with CSE

leftmost value<""
{=INDEX(range,MIN(IF(range<"",COLUMN(range)-MIN(COLUMN(range))+1)))}

rightmost<""
{=INDEX(range,MAX(IF(range<"",COLUMN(range)-MIN(COLUMN(range))+1)))}


--
regards,
driller

*****
- dive with Jonathan Seagull



"T. Valko" wrote:

That will work as long as the range is A1:F1 but it could fail if the
range
was in say, B1:G1.

INDEX "stores" the range values based on positions. Those positions are
1:n.
So, in your IF call the value_if_true must correspond with the INDEXED
positions.

If the indexed positions are 1:6 then:

column(a1:f1)

Must return an array from 1:6 (which it currently does).

However, if the range was B1:G1 then:

COLUMN(B1:G1) returns an array from 2:7. This could lead to incorrect
results.

So, what you'd need to do is calculate an offset so that the array
returned
is the same as the indexed positions:

COLUMN(B1:G1)-MIN(COLUMN(B1:G1))+1

That now returns an array from 1:6

=INDEX(B1:G1,MIN(IF(B1:G1<"",COLUMN(B1:G1)-MIN(COLUMN(B1:G1))+1)))

=INDEX(B1:G1,MAX(IF(B1:G1<"",COLUMN(B1:G1)-MIN(COLUMN(B1:G1))+1)))

--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
hi blackhole,

maybe u can try to use an array formula
assume your row_range of 6 cells are located on a1:f1

for the oldest (leftmost) data in a1:f1 (text or numbers)
{=index(a1:f1,1,min(if(a1:f1<"",column(a1:f1))))}

for the latest (rightmost) data in a1:f1 (text or numbers)
{=index(a1:f1,1,max(if(a1:f1<"",column(a1:f1))))}

*array entered with control+shift+enter...*
good luck
--
regards,
driller

*****
- dive with Jonathan Seagull



"blackhole" wrote:

I have price sheet dumped from SAP, which gives prices for a given
item
by
period. If no activity in a period, cell is blank. How do I get a
formula
to
return the first value in a row(left-most) (of 6 cells) in one cell,
and
then
the last value (right-most) in another? Represents old price, and last
price.