find first and last values in a row of cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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.
 
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
 
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)))
 
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
 
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.
 
so now i see why.. its perfectly clear and understood, eh! <bg>
--
regards,
driller

*****
- dive with Jonathan Seagull
 
Back
Top