find first and last values in a row of cells

B

blackhole

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.
 
C

CLR

In G2 put
=IF(A2,A2,IF(B2,B2,IF(C2,C2,IF(D2,D2,IF(E2,E2,F2)))))

In H2 put
=IF(F2,F2,IF(E2,E2,IF(D2,D2,IF(C2,C2,IF(B2,B2,A2)))))

Vaya con Dios,
Chuck, CABGx3
 
T

T. Valko

Try these:

For the first number in the range:

=IF(COUNT(A1:F1),INDEX(A1:F1,MATCH(TRUE,INDEX(ISNUMBER(A1:F1),0),0)),"")

For the last number in the range:

=IF(COUNT(A1:F1),LOOKUP(1E10,A1:F1),"")

Of course, if there is only 1 number in the range then it will be both the
first and last number.
 
D

driller

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
 
T

T. Valko

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)))
 
D

driller

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

T. Valko

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.
 
D

driller

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

*****
- dive with Jonathan Seagull
 
Top