Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
information filter
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
information filter
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. "scott" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
information filter
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,RO W()))) 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,RO W()))) 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 --- "scott" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
information filter
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
Macro to Filter Information in Multiple sheets and Make graph | Excel Worksheet Functions | |||
Copy filter Information | Excel Discussion (Misc queries) | |||
When i try to filter information, excel returns a blank page? | Excel Worksheet Functions | |||
filter and keep same information. | Excel Worksheet Functions |