YTLE#104: Find 1st Positive# in Excel List

Hi everyone,

Did anyone know how to find the first positive value in a set of value in column AB?

I have tried to find all the positive value in column AB and move it to column AC. But it return nothing.

My scenarios;
1) I have a set of years from 2006 until 2105 (99 years) in column AA (start with AA16)
2) A set of cash flows in column AB corresponding to column AA (start with AB16)

whereas the cash flows are from the formula:
=+SUMIF(A:A, "=" &AA16,Z:Z)

Now I want to find the first cash flow that turn positive in column AB but not succeed yet.

I refered the coding from http://www.mrexcel.com/archive2/49200/57208.htm


Code:

Private Sub OtherCashFlow_Click()
For I = 1 To 3

' Investment in New Business for MTP All (IL)
' --------------------------------------

inputfile_dir = ThisWorkbook.Worksheets("Info-Input").Range("B9").Value
inputfile_name = ThisWorkbook.Worksheets("Info-Input").Range("B10").Value + ".xls"
inputfile = inputfile_dir + inputfile_name

Worksheet_name = ThisWorkbook.Worksheets("Info-Input").Range("B14").Offset(I - 1, 0).Value

'Application.ScreenUpdating = False
    
 Dim starting_year As Range
    
    Workbooks.Open inputfile, UpdateLinks:=0
    ActiveSheet.Range("AA:AB").Select
    Selection.ClearContents
    
    'Column AA is year
    
     ActiveSheet.Range("AA15").Value = ActiveSheet.Range("A15").Value
        
        For s = 1 To 99
            t = Chr(65) & Chr(65) & 15 + s
            u = Chr(65) & Chr(65) & 14 + s
            
        ActiveSheet.Range(t).Formula = "=1+" + u
        
         v = Chr(65) & Chr(66) & 15 + s
        ActiveSheet.Range(v).FormulaR1C1 = "=+SUMIF(C[-27], ""="" &RC[-1],C[-2])"
        
        Next s
        
        ActiveSheet.Columns("AC:AD").Insert
        
    'Loop through needed cells only see if positive
    
    ActiveSheet.Columns("AB").Activate
    n = 16
    Do Until Cells(n, 28) = ""
    If Cells(n, 28) > 0 Then
    Cells(n, 29) = Cells(n, 28)
    End If
    n = n + 1
    Loop
 
Next I

End Sub

Can I know why it return nothing in column AC? It seems like didn't loop until blank cell in column AB and directly go the
Code:

Next I

Thank you very much

I have a column of numbers - Col A. Some of these are positive, some zero, and some negative. For each row in an adjacent column B, I would like to show the number of rows that I would have to go down to find the next positive value in Col A. For example:

Col A
3
2
-6
-3
-4
7
4

If I am on row 4, then I would have to go down 3 rows until I hit 7, the first positive. If I am on row 5 then I need to go down 2.

I was hoping to use soemthing like in cell B4:

MATCH(">0",A5:$A$8,2) but this doesn't work.

Can anyone help?

Thanks,

Mikeg

I'd like to identify the last positive number in a series of numbers that might end with 0 (zero). Want to exclude the zero. If the series of numbers shows 0, 3, 5, 2, 7, 0 I'd like the formula to identify 7 as the last number. Then I'll use MATCH to learn it's 5th number in the series.

ideas?

I have a series of accounts with values for 0-15 months in a row. Some of the months have a negative value. What I would like to do is first find the negative value, if any in the row and apply it to the first positive number in the row. The negative numbers always preced my positive numbers in the row. Some more info is the negative may be in month 0 and the next positive may be in month 10 or could be month 1. Any help? I'm stumped.

I am creating a payback analysis for a purchase that has variable cash flows. I have a cummulative total row that generally starts negative (cash outflow at purchase) and eventually becomes positive.

I need a formula that returns the month that the cummulative total becomes positive. Here is what i have so far, but it doesn't work.

List of cummulative numbers:
Month 1: -170k
Month 2: -220k (additional purchase)
Month 3: -50k (income from purchase)
Month 4: -25k
Month 5: 5k (Cash Flow Positive - Return Month 5)
Month 6-20: >5k

My formula is: MATCH(0,$A$1:$H$1,1). This returns 4. Is there no way to make it ruturn month 5 without setting up an Index(Match).

Excel help says that "If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on." I cannot sort the list, I want the formula to be automated.

Any ideas?

Hi,

I have a price calculator with sererval tabbed sheets the quantities remain 0 until and item is calculated.

I want the information to be displayed on a seperate table so that all the items with quantities above 0 are displayed on front sheet.

how do i do this?

Thanks

Hello

I'm trying to find the number farthest from 0 .. whether is is farthest negatively or postively.

It seems like small and large array wouldnt work for this? Or am i wrong?

So let a = -334
and let b = 778

If i'm display "top distance from being even (O) how could i find that?

Thanks so much!

Hello,

I am looking for a formula that can find the the value of the last positive value in a column and the sum up until said last positive value.

Example:

Column A
1
2
3
4
-5
-7
-8

so the formula, whatever it may be, should be noticing that the last positive number is 4 and finding the sum from the top of the column to the last positive number. So the formula should give the value of 10.

I would greatly appreciate any and all help in this matter!

Thank you all!

Hello all

I have two columns of data, which are constantly being added to. Column A is a unique name (no duplicates) and column B is its corresponding value. In many instances the value in column B is zero. I want to produce a list in column C with the names that have a positive value which will update automatically as names and values are added to column A and B. Is this possible?

Can anyone provide a formula to lookup the smallest positive and smallest negative values in a range? ie, the positive and negative numbers in the range closest to zero.

Thanks