SMALL function to exclude a row

[jking1]

Hi,

I am attempting to calculate the lowest week of sales over a period of time (which is easy enough) however I need to exclude 1 row from the range. Reason for this is that the period in question is known to have reduced sales for a 1 week period & therefore I would like to exclude this one row from the SMALL or MIN function but to include all other data. I am hoping that someone may be able to help with this. Attached is a test set of data I have produced. The yellow highlighted cell (D10) is the row I would like to exclude.

Many thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How would you know which week was to be ignored?

 

[jking1]

Hi & Thanks!
In the schedule it is the week of Christmas I wish to ignore. The business still trades over this period but at very minimum levels.

 

[Fluff]

As your dates are all in January & you want to exclude the 6th, that doesn't really make much sense.
That said, how about

+Fluff 1.xlsm
	A	B	C	D
1
2
3
4
5		01/01/2021		8
6		02/01/2021		2
7		03/01/2021		3
8		04/01/2021		0
9		05/01/2021		5
10		06/01/2021		1
11		07/01/2021		3
12		08/01/2021		21
13		09/01/2021		0
14		10/01/2021		5
15		11/01/2021		0
16		12/01/2021		5
17
18
19		06/01/2021		2
Report

Cell Formulas
Range	Formula
D19	D19	=MINIFS(D5:D16,D5:D16,">0",B5:B16,"<>"&B19)

 

[jking1]

Apologies, I should've made it more relevant!

That has worked an absolute treat! Many thanks for this.

(I have a feeling this website will become extremely valuable...)

 

[Fluff]

Glad to help & thanks for the feedback.

 

[jking1]

Out of curiosity, if i wanted to increase the range from a single cell to 2 cells, what would I amend the formula too?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2
3
4
5		01/01/2021		8
6		02/01/2021		2
7		03/01/2021		3
8		04/01/2021		0
9		05/01/2021		5
10		06/01/2021		1
11		07/01/2021		1
12		08/01/2021		21
13		09/01/2021		0
14		10/01/2021		5
15		11/01/2021		0
16		12/01/2021		5
17
18
19		06/01/2021		2
20		07/01/2021
Report

Cell Formulas
Range	Formula
D19	D19	=MINIFS(D5:D16,D5:D16,">0",B5:B16,"<>"&B19,B5:B16,"<>"&B20)

 

[jking1]

That's great, many thanks!

 

[Fluff]

You're welcome.