SMALL function excluding specific cell

[plotting]

https://1drv.ms/x/s!ArHhL08110anmBjx0Xyj3khzBu0S

Here is a link to the data set, sheets1:5 have random numbers in A1:N10

On sheet6 it uses =SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)) to =SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:20)) to get the 20 smallest numbers in the range, wondering anyone has a formula, array perhaps, to ignore sheet5 cell F8 for example.

Thanks in advance for looking!

 

[oldbrewer]

I highlighted A1:A3 then with control key held down clicked C1 C2 C3 B1 B3 then insert name MYRANGE

 

[plotting]

Try (no need for helper column)
=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)+(Sheet5!$F$8<=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))))

If all sheets have similar name this ARRAY formula works.
In A2 then drag down.

<sheet5!$f$8,0,sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!$f$8))))[ code]

=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)+IF(SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))< Sheet5!$F$8,0,SUM(COUNTIF(INDIRECT("Sheet"&ROW($1:$5)&"!A1:N10"),Sheet5!$F$8))))

<sheet5!$f$8,0,rows($1:1)+sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!c8))))[ code]
How ARRAY formula is entered


Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.</sheet5!$f$8,0,rows($1:1)+sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!c8))))[></sheet5!$f$8,0,sum(countif(indirect("sheet"&row($1:$5)&"!a1:n10"),sheet5!$f$8))))[>

Both of these worked great, thanks guys

 

[Peter_SSs]

Both of these worked great, thanks guys

Are you sure?

With your sample file, the smallest number in sheets 1:5 is 25 and that is in cell F8 of Sheet5. Both formulas do correctly exclude that cell from the 'small' list. However, now, in say sheet 5, change cell F7 to 25 so that both F7 and F8 are 25.
In the small list, my formula now includes the 25 from F7 but excludes the 25 from F8 as required.
The other formula excludes both 25 values from the list. That is, it excludes more cells than just Sheet5, F8
If you change cells in other sheets to 25, those cells also get excluded by the post 10 formula, but are not excluded by my formula as you said you only wanted to exclude one cell.

One further thing to be watchful for if using the post 10 formula. After entering the formulas if you decide to subsequently insert a new row at the top of the sheet containing the formulas for some reason (say to add a heading row), the formulas would then be trying to evaluate the small values in Sheet2:Sheet6 not Sheet1:Sheet5

 

[plotting]

Try (no need for helper column)
=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1)+(Sheet5!$F$8<=SMALL(Sheet1:Sheet5!$A$1:$N$10,ROWS($1:1))))

Are you sure?

With your sample file, the smallest number in sheets 1:5 is 25 and that is in cell F8 of Sheet5. Both formulas do correctly exclude that cell from the 'small' list. However, now, in say sheet 5, change cell F7 to 25 so that both F7 and F8 are 25.
In the small list, my formula now includes the 25 from F7 but excludes the 25 from F8 as required.
The other formula excludes both 25 values from the list. That is, it excludes more cells than just Sheet5, F8
If you change cells in other sheets to 25, those cells also get excluded by the post 10 formula, but are not excluded by my formula as you said you only wanted to exclude one cell.

Interesting, I didn't even try that, thanks for testing for me, there is definitely a chance that F8's value is included in the range somewhere else!

 

[Peter_SSs]

Interesting, I didn't even try that, thanks for testing for me, there is definitely a chance that F8's value is included in the range somewhere else!

You are welcome.
Of course in a worst-case scenario, if all 140 cells on all 5 sheets were all identical, the formulas would return no values at all, instead of the expected 699 values from the other cells.