Removing #N/A from Array

[Tigerexcel]

How do you remove the #N/A from this array, have tried IFNA as well to no avail. The 7 in E7 refers to the top 7 sales.

Book1
	A	B	C	D	E
1	Sales			Top	7
2	85	25		99
3	52	92		97
4	5	88		96
5	59	15		92
6	67	45		92
7	81	29		88
8	50	92		85
9	45	8		#N/A
10	53	68		#N/A
11	1	74		#N/A
12	97	79		#N/A
13	40	43		#N/A
14	13	5		#N/A
15	39	96		#N/A
16	73	62		#N/A
17	99	63		#N/A
Sheet2

Cell Formulas
Range	Formula
D2:D17	D2	=IFERROR(LARGE($A$2:$B$17,ROW(INDIRECT("1:"&E1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AhoyNC]

Try:

Book1
	A	B	C	D	E
1	Sales			Top	7
2	85	25		99
3	52	92		97
4	5	88		96
5	59	15		92
6	67	45		92
7	81	29		88
8	50	92		85
9	45	8
10	53	68
11	1	74
12	97	79
13	40	43
14	13	5
15	39	96
16	73	62
17	99	63
Sheet2

Cell Formulas
Range	Formula
D2:D16	D2	=IF(ROWS($D$2:D2)>$E$1,"",AGGREGATE(14,6,$A$2:$B$17,ROWS($D$2:D2)))

 

[Tigerexcel]

Thanks Ahoy, that works fine but does that mean you can't use the IFERROR function with arrays?

 

[AhoyNC]

Actually your formula worked for me (see below). I just used the AGGREGATE which handles arrays without the need for CTRL-SHIFT-ENTER. I also took out the volatile function INDIRECT which can slow things down in a large data set (probably doesn't matter in this case).

Book1
	A	B	C	D	E
1	Sales			Top	7
2	85	25		99
3	52	92		97
4	5	88		96
5	59	15		92
6	67	45		92
7	81	29		88
8	50	92		85
9	45	8
10	53	68
11	1	74
12	97	79
13	40	43
14	13	5
15	39	96
16	73	62
17	99	63
Sheet1

Cell Formulas
Range	Formula
D2:D17	D2	{=IFERROR(LARGE($A$2:$B$17,ROW(INDIRECT("1:"&E1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[Phuoc]

Try:

=IF(ROW(INDIRECT("1:"&ROWS(B2:B17)))>E1,"",LARGE($A$2:$B$17,ROW(INDIRECT("1:"&E1))))

 

[Tigerexcel]

Actually your formula worked for me (see below). I just used the AGGREGATE which handles arrays without the need for CTRL-SHIFT-ENTER. I also took out the volatile function INDIRECT which can slow things down in a large data set (probably doesn't matter in this case).

Book1
	A	B	C	D	E
1	Sales			Top	7
2	85	25		99
3	52	92		97
4	5	88		96
5	59	15		92
6	67	45		92
7	81	29		88
8	50	92		85
9	45	8
10	53	68
11	1	74
12	97	79
13	40	43
14	13	5
15	39	96
16	73	62
17	99	63
Sheet1

Cell Formulas
Range	Formula
D2:D17	D2	{=IFERROR(LARGE($A$2:$B$17,ROW(INDIRECT("1:"&E1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

So just to clarify, my original formula worked ok and didn't return the #N/A errors?

 

[Peter_SSs]

Actually your formula worked for me (see below).

I suspect the OP selected the whole range D2:D17, entered the formula once and confirmed into all cells at once with C+S+E whereas you entered a formula in one cell only then copied down ?

 

[Peter_SSs]

@Tigerexcel
The IFERROR does not work because there are no errors in the formula. If you select any cell in your formula range (value or error cell) & use the formula evaluation tool you end up at the penultimate step with what is shown below. As you can see, there are no errors, but of course only 7 values. So the error is that you are trying to put a 7-value array into a 16-cell range.

 

[Tigerexcel]

Ok thanks for explaining that Peter, IFNA produces the same thing so using this particular combination of functions I'll have to live with the #N/A?

 

[Peter_SSs]

I'll have to live with the #N/A?

No, you can use a function like AhoyNC suggested in post #2 and just copy it down.