Complex array formula

andrei · #1 02-03-2012, 02:47 AM

Hello Colin,

No need to bother as I get the result using the complicated formulas from F2 to J2; the problem came when trying to simplify the formula and you explained very well
why the error occurred.

Regards,
Andrei

Colin Legg · #2 02-03-2012, 03:17 AM

Hi Andrei,

Fair enough, if you're happy then I'm happy.

Incidentally, if you happened to want to extract a list of values greater than zero, in the order that they appear, then I've attached an example for you.

Code:

=IF(COUNTIF($B2:$F2,">0")>=(COLUMN(G2)-COLUMN($G2)+1),
    INDEX($B2:$F2,SMALL(IF($B2:$F2>0,COLUMN($B2:$F2)-COLUMN($B2)+1),COLUMN(G2)-COLUMN($G2)+1)),"")

Regards,
Colin

Thread Tools
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Complex Find and Replace	paulkaye	Word	1	11-13-2011 04:23 AM
Modifying subject in Hyperlink - Complex	amritr	Outlook	0	05-26-2011 11:20 PM
Searching for Word Reformatter for Complex Docs	jchapman	Word	1	05-05-2011 04:50 PM
Look up an array based on user input	johnsmb	Excel	2	01-07-2011 01:12 PM
Complex Document Formatting	rguima	Word	0	02-22-2009 04:31 PM