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
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