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.

 

[Peter_SSs]

I don't know why you would be, but if you are determined to use that block array method and your original formula, you could use Conditional Formatting just to hide the error values.
Below I have used red font but if you used white font then the errors at least would not be visible.

20 07 14.xlsm
	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
Sheet5

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.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D17	Expression	=ISNA(D2)	text	NO

 

[Tigerexcel]

Thanks for the last formula Peter, I didn't know you could do that with conditional formatting.
All things considered it might be easier to learn the AGGREGATE function. Appreciate the advice.

 

[Peter_SSs]

All things considered it might be easier to learn the AGGREGATE function.

It is a very useful function & I would recommend learning about it. However, it doesn't do anything special for you for this exercise, you could just as easily stick with using LARGE with this formula, copied down.

20 07 14.xlsm
	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
Sheet6

Cell Formulas
Range	Formula
D2:D17	D2	=IF(ROWS(D$2:D2)>E$1,"",LARGE(A$2:B$17,ROWS(D$2:D2)))