Excel

  Kypros 13:37 18 Feb 03
Locked

I need a formula which will detect the first instance of a number which is less than 0 in a column of numbers and return that number and a number from the cell 4 cells to the left of it, and place those numbers in two cells.
Example: find first instance of negative number in array F5:F50 and place in D1. Find corresponding value from cell in column B and place in D2.
Can anyone help out here?

  recap 19:21 18 Feb 03

refresh

  Paroxetine 19:21 18 Feb 03

have you tried asking Mr excel.com? they can be quite helpful.

Also there was a posting in here a few months back about a spreadsheet that would show u all formulas and how to use them. Maybe that could be used as a foundation to build on something.

Sorry I cant be much help, my maths are bad as it is without getting all confused about formulas and such!

P

  VoG™ 19:29 18 Feb 03

You need to use either the VLOOKUP worksheet function od a combination of INDEX and MATCH - I think the latter if you want to put something to the left of the column that you are searching.

As an example of the formula construction:

=INDEX(Products!$B$4:$B$11,MATCH(LEFT(B6,2),Products!$C$4:$C$11,0))

Sorry, I have to logoff now; back later. Hopefully this will get you started. Look these functions up in Help.

  recap 19:39 18 Feb 03

no problem, I thought you might like to get your teeth into this one :-)

regards, recap

  VoG™ 22:36 18 Feb 03

Having looked again at this problem I cannot see a way of solving it easily using the standard worksheet functions. Therefore the way forward is to create two UDFs (User Defined Functions).

ALT+F11 to enter the Visual Basic Editor

Insert/Module and paste in the following:

Function LK1()

Application.Volatile

Dim iRow As Integer

For iRow = 5 To 50

If ActiveSheet.Cells(iRow, 6) < 0 Then Exit
For

Next iRow

LK1 = ActiveSheet.Cells(iRow, 6).Value

End Function


Function LK2()

Application.Volatile

Dim iRow As Integer

For iRow = 5 To 50

If ActiveSheet.Cells(iRow, 6) < 0 Then Exit For

Next iRow

LK2 = ActiveSheet.Cells(iRow, 2).Value

End Function


Now ALT+F11 to exit the VBE.

In D1 enter the formula

=lk1()

and in D2 enter the formula

=lk2()


Note: there is no error checking - if no negative value is found you will end up with the values corresponding to F50.

  Kypros 11:06 19 Feb 03

Thanks, but I found that a little too complicated (I am not very good with these things). I got two good answers from Mr Excel from Aladin Akyurek and Phamtom1975 which were just about OK for me to grasp.

click here

  VoG™ 11:45 19 Feb 03

Aladin's solution is the best of those two.

I knew there was a way using INDEX and MATCH but couldn't quite get my head around it. I jumped on the VBA wagon, just like Phantom1975.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…