| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Exclude #N/A values and Return Numeric values to consecutive cells in Single Row
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Sam via OfficeKB.com" <u4102@uwe wrote in message news:7f72b56b9744e@uwe... Hi Biff, That's Brilliant! Your last formula provided the required result. Thank you very much for your time and help. T. Valko wrote: Assume you want the results starting cell in C53: Array entered** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),INDEX($C 50:$J50,SMALL(IF(ISNUMBER($C50:$J50),COLUMN($C50:$ J50)-MIN(COLUMN($C50:$J50))+1),COLUMNS($C53:C53))),"") Copied across. If the values are in ascending order as is depicted in your sample data then you can use a less complicated array formula** : =IF(COLUMNS($C53:C53)<=COUNT($C50:$J50),SMALL(IF (ISNUMBER($C50:$J50),$C50:$J50),COLUMNS($C53:C53)) ,"") Even though my values were not in ascending order, the above, less complicated formula returned my numeric values in ascending order. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Cheers, Sam -- Message posted via http://www.officekb.com |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return Unique Consecutive Duplicate Values across Single Row | Excel Worksheet Functions | |||
| Return Unique Duplicate Numeric Values across Single Row | Excel Worksheet Functions | |||
| Return Numeric Labels that have Values =4 across Single Row | Excel Worksheet Functions | |||
| Return Summed Count of Multiple Consecutive Numeric Values | Excel Worksheet Functions | |||
| Return Single Instance of Numeric Values from a Column | Excel Worksheet Functions | |||
Threaded Mode
