find first and last value in a row and return it

[Jonsson]

Hi,

I wonder if anyone can help me with a formula that lookup the first cell in
a row that occures when the sum is greater than or equal with 1.

Sometimes the formula below is returning zero, and sometimes it´s returning
the real value, all depending if N5>1 or not. I want to get the first value
that occurs when N5>1 in a target cellrange "B1".

=IF(N5<1;"";(N3-N4))

And vice versa to return the value that is last in the row to be > 1

Thanks in advance

//Thomas

 

[Frank Kabel]

Hi Thomas
not quite sure what you want. If I understood you correctly you want
to get the row numbers of:
- the first cell in column B where the value >1
- the first cell in column B where the value >2

One way: Try the following array formulas (enter with CTRL+SHIFT+ENTER)
=MIN(IF(B1:B1000>1,ROW(B1:B1000),10000))
and
=MAX(IF(B1:B1000>1,ROW(B1:B1000),0))

 

[Bob Phillips]

Thomas,

Frank's solution gives the row number. To get the value, try

=INDEX(B1:B1000,MIN(IF(B1:B1000>1,ROW(B1:B1000),1)),1)
and/or
=INDEX(B1:B1000,MAX(IF(B1:B1000>1,ROW(B1:B1000),0)),1)

Both are array formulae, so commit with Ctrl-Shift-Enter.

The first fails if there are empty cells in the range, and neither takes an
action if there are no items > 1, but your formula can handle that

=IF(INDEX(B1:B1000,MAX(IF(B1:B1000>1,ROW(B1:B1000),0)),1)>1,(N3-N4),"")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
I think the first formula should read
=INDEX(B1:B1000,MIN(IF(B1:B1000>1,ROW(B1:B1000),10000)),1)
otherwise always the value from row 1 is returned

 

[Bob Phillips]

Frank,

I did that deliberately (should have mentioned that), as no match should not
return 1000 otherwise it looks like a match, and (more importantly <vbg>),
my formula then fails as B1000 had no data in my test data.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Frank,

I did that deliberately (should have mentioned that), as no match
should not return 1000 otherwise it looks like a match, and (more
importantly <vbg>), my formula then fails as B1000 had no data in my
test data.

Now thats a reason <vbg>