Match first positive or negative number in non-sequential range

Joined
Apr 21, 2008
Messages
3
Reaction score
0
Hi,

I have a table thus:

Field A Field B Jan Feb Mar Apr
Red 1000 0 -10 -50 -100
Blue 500 50 70 -20 -40
Green 1000 -100 -150 -200 75

For each line I want a formula to return the month in which the first negative value appears.

i.e. for "Red" I want to return column 4 which will give me Feb
i.e. for "Blue" I want to return column 5 which will give me Mar
i.e. for "Green" I want to return column 3 which will give me Jan

Have tried using MATCH but as the values are rarely in ascending/descending order it gives inconsistent results.

Once I get this working I'll be looking to modify it to find the first positive number in the same range (i.e. ignoring zeros and text).

Driving me bonkers....

Thanks very much
Yard
 
Joined
Apr 21, 2008
Messages
3
Reaction score
0
Have managed to work out a solution, but it requires having a set of columns elsewhere in the sheet which use RANK to put the values in the row in order, then I can use a MATCH to find the rank of the next value to zero (i.e. the first negative number).

Still can't help thinking there must be a more elegant solution - it feels like a simple thing to (want to) do!!

Cheers
 
Joined
Apr 21, 2008
Messages
3
Reaction score
0
No further joy after trying again last night either...

I guess there's a VB function solution, but I'm not keen on this as the workbook may go to other users and experience tells me that functions/macros need a good bit of "user-proofing" when distributed!

Anyone have any thoughts?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top