[Solved] Formulas: List of positive values

**Andy Pope** · Jul 29th 2003

Hi all,

I'm looking for a set of formula to reduce a list of values to a list of values greater than zero.
e.g.

Code

Orig    New
    1    1
    3    3
    0    9
    9    6
    6    2
    0    0 ( or NA() or "")
    2    0 ( or NA() or "")

Ideally the reduced list would be in the same order as the original.
VBA is not an option.

Cheers
Andy

Richie(UK) · Jul 29th 2003

Hi Andy,

Does it have to be via formulas?

Just wondering whether the AutoFilter is an option?

Or, assuming the data starts at A1, enter in B1 =IF(A1>0,1,""), then sort by column B.

Any use?

Anonymous · Jul 29th 2003

I'm thinking similarly to Richie, but wonder if a pivot table is an option. The attached creates an ordinal list of the original numbers and uses this in a pivot table after IF>0 filtering to create the new filtered list in column B of the pivot table.

Is this of any use?

**Andy Pope** · Jul 29th 2003

Hi Richie,

Edit: and thomach,

No I'm really looking for a formula only solution.

If I have to resort to auto filters, sorting or any other multi step solution I may as well go the vba Route.

I already have a udf that appears to work.

Code

Public Function udfFirstActive(MyList As Range, Position As Long) As Variant
'
' Return First value greater than zero from MyList
'
    Dim lngIndex As Long
    Dim lngCount As Long
    
    For lngIndex = 1 To MyList.Rows.Count
        If MyList.Cells(lngIndex, 1).Value &gt; 0 Then
            lngCount = lngCount + 1
        End If
        If lngCount = Position Then
            udfFirstActive = MyList.Cells(lngIndex, 1).Value
            Exit Function
        End If
    Next
    udfFirstActive = 0
    
End Function

Display More

Thanks anyway.

Cheers
Andy

Egad · Jul 29th 2003

Hi Andy, I've used formulas only but needed 2 columns. It would be nice to get a 1 column solution. The key seems to be to pre-determine the # of zeros.

If your data is in a1:a7:
put this in b1 and copy down:
=MATCH(SMALL(A$1:A$7,COUNTIF(A$1:A$7,0)+ROWS(A$1:A1)),A$1:A$7,0)
and put this in c1 and copy down:
=INDEX(A$1:A$7,SMALL(OFFSET(B$1,0,0,COUNTIF(A$1:A$7,">0"),1),ROWS(A$1:A1)),1)

Anonymous · Jul 29th 2003

Egad,

I've got to learn to understand the MATCH function, so I may have done something stupid. Anyway, when I tried your formulas against the dataset I'd created and it parses the list correctly, but the ordering is lost. Should it have been sustained?

See the attached.

Egad · Jul 29th 2003

Thanks Thomach, back to the drawing board and no soup for me.
It does keep the order but groups all duplicates together. :mad:
ie
2
0
4
3
5
2
becomes
2
2
4
3
5

**Andy Pope** · Jul 29th 2003

Hi guys,

Yep, the MATCH/INDEX combination works until you present it with duplicate values.

Unfortunately I do need to be able to identify which value is which.

At least this is not a simple question.
I was half expecting my question to give me one of those DOH! moments ;;)

Cheers
Andy

Egad · Jul 29th 2003

Ok, lets try again, this should work for integers but the approach could be modified to account for decimals.

data in column A

b1:
=A1+(ROWS(A1:A$1)/1000*(A1<>0))
c1:
=MATCH(SMALL(B:B,COUNTIF(B:B,0)+ROWS(B$1:B1)),B:B,0)
d1:
=INT(INDEX(B:B,SMALL(OFFSET(C$1,0,0,COUNTIF(B:B,">0"),1),ROWS(B$1:B1)),1))

:banghead:

**Andy Pope** · Jul 29th 2003

:congrats: Bravo Egad :congrats:

Exactly what I needed.

The attached, auto sizing pie chart, will show you how I was intending to use this.

Thank you all for your efforts.

Cheers
Andy

[Solved] Formulas: List of positive values

Participate now!

Share

Users Viewing This Thread