Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 208
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 751
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CHANGE A ROW OF POSTIVE NUMBERS TO NEGATIVE NUMBERS Harlan Excel Discussion (Misc queries) 3 December 1st 06 06:04 PM
How can i give formula to count the only numbers except zero in a Anuja Excel Discussion (Misc queries) 7 November 15th 06 07:52 AM
Give RELEVANT responses to questions. DO NOT give usless list pmartin Excel Worksheet Functions 2 July 6th 06 06:08 PM
why does dividing numbers give me dates and not numbers? dylan Excel Discussion (Misc queries) 1 June 21st 06 11:40 PM
How to change a series of positive numbers to negative numbers Ellie Excel Worksheet Functions 5 September 5th 05 05:10 PM


All times are GMT +1. The time now is 04:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"