Help with formula
 

Hi I have a formula that I am using and it works I just need to know what all
of the bits f it mean and HOW it works.

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT("1:15")),A1:C 1,0)),ROW(INDIRECT("1:15"))),1)

it is an array formula.

Thanks in advance

Help with formula
 

Hi Chris

This bit

ROW(INDIRECT("1:15"))

generates this array

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}

then MATCH(ROW(INDIRECT("1:15")),A1:C1,0)

generates another array based on whether 1 to 15 appears in the range
A1:C1 - if it does you get the position, if not #N/A so if A1=6, B1=2,
C1=11 you get

{#N/A;2;#N/A;#N/A;;#N/A;1;#N/A;#N/A;#N/A;#N/A;3;#N/A;#N/A;#N/A;#N/A}

ISERROR converts this to

{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TR UE;FALSE;TRUE;TRUE;TRUE;TRUE}

[although you only really require ISNA]

Now the IF part looks like

IF({TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE ;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},ROW(INDIRECT("1:1 5")))

which becomes

{1;FALSE;3;4;5;FALSE;7;8;9;10;FALSE;12;13;14;15}

Now the whole formula is reduced to

=SMALL({1;FALSE;3;4;5;FALSE;7;8;9;10;FALSE;12;13;1 4;15},1)

so that returns the smallest value from that range, i.e. 1

In short it should return the smallest value 1 to 15 which doesn't
appear in A1:C1.

This specific formula can never return a value greater than 4 so some
parts of it are superfluous, perhaps you use other versions with a
larger range or using =SMALL(range,2) or =SMALL(range,3)?

If you only ever use this specific formula I think you could simplify
it to

=MATCH(TRUE,ISNA(MATCH({1;2;3;4},A1:C1,0)),0)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=522905