Find the first higher value than a value or find the first lower value than a value

[egotajcs]

Hi guys,

I was searching over the Internet for a solution, and I found something but not enough, and I cannot solve my problem.
So decided to register here and open a new topic about it. (and tried to give the thread a google-friendly name).

So, I have an array, like this:

Please Login or Register  to view this content.

Let's assume that these numbers are in the column A.

I figured out reading the net if I want to find the first higher than - let's say - 4 then my formula should be this:

=INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15>4;0),0))

And if I want to find the first lower than for example -2 (minus 2) is this:

=INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15<-2;0),0))

But what can I do, if I don't know in advance the values, so excel gives an error in return? For example using this with the above numbers:

=INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15>10,0),0))

Anyway, my main question would be this: let's find the first value higher than 4 OR the first value lower than -2? And solve the error problem if none of them are true... Because the numbers can vary and we don't know if the return can be false because there is no higher or lower at all, because it is volatiling around zero...

Thank you, guys!

[TheCman81]

Hi and welcome

You can use the following formulas to return a blank if the value is not there

PLease note: I would recommend that you use a cell to input your value instead of hard coding 4, -2., 10 etc into the formula

Excel 2003

=IF(ISERROR(INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15>10,0),0))),"", INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15>10,0),0)))

Excel 2007 +

IFERROR(INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15>10,0),0)),"")

[egotajcs]

Thanks, I'll try it in the evening. As a matter of fact, this is my formula, but I didn't want to bother you all guys with the details (such as my windows is hungarian, so we use ; instead of , and of course the formulas are in hungarian too...)

=INDEX(H10:H93;HOL.VAN(IGAZ;INDEX(H10:H93<$A$8;0);1))

Anyway, I'll look forward to it in the evening.

[egotajcs]

Hi TheCman,

I adapted the formula to my excel and in a certain circumstance it returns with an error.
This is my formula now:

=IF(ISERROR(INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15<-2,0),0))),"",INDEX(A1:A15,MATCH(TRUE,INDEX(A1:A15>4,0),0)))

If there is no a value above +4 only under -2 it comes back with a missing error... When it is quite the opposite (there is no under -2 only above +4) it works fine...

PS: I'm using Excel2003...

[oeldere]

If you post an excel file, without confidential information, we can add the formula in the file.

The formula will translate in hungarian, when you open the file.

[egotajcs]

Yes, of course, here it is.
Change the yellow cells. The purpose is to stop at the first max or at the first min from top to the down...

Here's the file: [ATTACH]269024[/ATTACH]

[oeldere]

Another approach

With helpcolumns and Vlookup

[egotajcs]

Can this be converted to xls?

[egotajcs]

After converting (with Zamzar) it has some errors too, see:

http://s17.postimg.org/6d6ky1vof/example_egotajcs.jpg
'hiányzik' means 'missing', 'név' means 'name.'

[oeldere]

Excel 2003 version.

[egotajcs]

But I think, you misunderstood my problem.
The relevant column is the 'sum' column. So it can go up and down constantly (caused by the volatile column). I would like to find the first higher value in the 'sum' column OR the first lower value in the 'sum' column, based on two values: the minimum value, which is a negative value (for example -20) and a maximum value (which can be for example +10). Both changeable values are the yellow cells.

TheCman81's post (http://www.excelforum.com/excel-form...ml#post3427144) almost worked, but I have some errors, if the values aren't fit...

[egotajcs]

So if we change the minimum cell (D1) to -5 and the maximum cell (D2) 5 then the result should be: 5.15
If we change the minimum cell (D1) to -10 and the maximum cell (D2) 30 then the result should be: -12.19
If we change the minimum cell (D1) to -20 and the maximum cell (D2) 15 then the result should be: 15.65
If we change the minimum cell (D1) to -30 and the maximum cell (D2) 30 then the result should be: it would give an error, because there are no lower than -30 or higher than +30, so it can be a note, or the last cell, or anything but it shouldn't be an excel error, because it stops the working...

[oeldere]

I think I understood your question correct.

That is what the formula in cell H2 does,

change e.g. cell d7 in 21 and see the change of the result (in cell H2).

please reply.

[egotajcs]

Hmmm., indeed. I just confused that the Column A examines the volatile column...

Okay, so this solution needs a 'temporary' column. I am fine with that. Thank you very much, Oeldere!

[oeldere]

Thanks for the reply.

Glad I could help.

You can delete the data on column A.

I wasn't sure which column you need the data from.

You can hide the column (B) if you want.

[egotajcs]

I truly thank you!