Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scan a series of numbers and when it turns postive give me the yea
Hi,
I have two rows; one is a row of years and the other is a series of negative to positive values (goes from negative then positive) for each year. i.e. 2002,2003,2004,2005,2006,2007,2008 -100, -75, -33,-12, 5, 8, 50 The values row will change over time based on other formulas. (they're a series of cash flows) so if I had to do it manually, based on the sample above, for the first year the values turned positive, the answer would be year 2006. But of course the values will change as they are formulas. So how can I set up a function to give me in a designated cell the first year that the values turn from negative to positive. I was thinking hlookup but that depends on a specific value such as 0 so I am not sure. Can you help? thank you very much Craig |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scan a series of numbers and when it turns postive give me the yea
This relatively simple formula will work if you already have one
positive value. =INDEX(A1:AZ1,MATCH(0,A2:AZ2,1)+1) How likely is it to get a 0 before going positive? HTH Kostis Vezerides On Feb 15, 6:43 pm, Craig wrote: Hi, I have two rows; one is a row of years and the other is a series of negative to positive values (goes from negative then positive) for each year. i.e. 2002,2003,2004,2005,2006,2007,2008 -100, -75, -33,-12, 5, 8, 50 The values row will change over time based on other formulas. (they're a series of cash flows) so if I had to do it manually, based on the sample above, for the first year the values turned positive, the answer would be year 2006. But of course the values will change as they are formulas. So how can I set up a function to give me in a designated cell the first year that the values turn from negative to positive. I was thinking hlookup but that depends on a specific value such as 0 so I am not sure. Can you help? thank you very much Craig |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
scan a series of numbers and when it turns postive give me the yea
=INDEX(A1:Z1,MATCH(TRUE,A2:Z20,0))
ctrl+shift+enter, not just enter "Craig" wrote: Hi, I have two rows; one is a row of years and the other is a series of negative to positive values (goes from negative then positive) for each year. i.e. 2002,2003,2004,2005,2006,2007,2008 -100, -75, -33,-12, 5, 8, 50 The values row will change over time based on other formulas. (they're a series of cash flows) so if I had to do it manually, based on the sample above, for the first year the values turned positive, the answer would be year 2006. But of course the values will change as they are formulas. So how can I set up a function to give me in a designated cell the first year that the values turn from negative to positive. I was thinking hlookup but that depends on a specific value such as 0 so I am not sure. Can you help? thank you very much Craig |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHANGE A ROW OF POSTIVE NUMBERS TO NEGATIVE NUMBERS | Excel Discussion (Misc queries) | |||
How can i give formula to count the only numbers except zero in a | Excel Discussion (Misc queries) | |||
Give RELEVANT responses to questions. DO NOT give usless list | Excel Worksheet Functions | |||
why does dividing numbers give me dates and not numbers? | Excel Discussion (Misc queries) | |||
How to change a series of positive numbers to negative numbers | Excel Worksheet Functions |