information filter

[scott]

I want to pull information from a list of data, but only the data that is
greater than 500,000. In c1:c30 i have data and c13, c16, c28, c29 are less
than 500,000. I i want to create a new list adjacent to the existing one
using only the values greater than 500,000 what function would i use? i was
thinking:

if(c1<500,000,"",c1) but this would leave that particular cell blank.

thanks for the help

 

[Sheeloo]

I can't think of any formula but you can do this with Data->Filter->Advanced
Filter

Assuming the lable of column C is amount
then enter anywhere in a blank area
Amount

500000

Then chose these two cells as Criteria Range in the Advanced Filter.

 

[Max]

Source data assumed in cols A to C,
from row1 down with key col = col C

In D1:
=IF(ISTEXT(C1),"",IF(C1<500000,"",ROW()))
This is the criteria col

In E1:
=IF(ROW()>COUNT($D:$D),"",INDEX(A:A,SMALL($D:$D,ROW())))
Copy E1 to G1. Select D1:G1, copy down to cover the max expected extent of
source data. Minimize col D. Cols E to G returns only the source lines from
cols A to C which satisfy the criteria, all neatly bunched at the top as
desired.

If you need only to return col C, then just put in E1:
=IF(ROW()>COUNT($D:$D),"",INDEX(C:C,SMALL($D:$D,ROW())))
then copy C1:E1 down to cover the max expected extent of data in col C.
Col E will return the required results
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik

 

[Max]

Typo, C1:E1 should read as D1:E1 in this line:

then copy C1:E1 down to cover the max expected extent of data in col C.

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---