How to exclude certain data points

[peterh2o]

I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.? For example:

1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75

I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays. Thanks!

 

[Domenic]

Assuming that A2:B7 contains the data, and G2:G10 contains a list of holidays, let D2 contain 5 (indicating a 5 day query), then try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(ROW($A$2:$A$7)<=SMALL(IF(ISNA(MATCH($A$2:$A$7,$G$2:$G$10,0)),ROW($A$2:$A$7)),D2),IF(ISNA(MATCH($A$2:$A$7,$G$2:$G$10,0)),$B$2:$B$7)))

Adjust the ranges accordingly, and amend the list of holidays and its reference, as needed. Replace AVERAGE with MAX, MIN, and STDEV...

Hope this helps!

 

[peterh2o]

Domenic-
Sorry but I should have mentioned that the dates and data are in two separate columns.

 

[Domenic]

Domenic-
Sorry but I should have mentioned that the dates and data are in two separate columns.

Yes, the solution offered is based on that understanding...

 

[peterh2o]

Domenic-
Thanks so much for the help with this. I have one final question regarding this formula, is there a way so that it takes the 30 days from the top to bottom because it seems as though it's taking the 30 days from the bottom of my list. I hope this makes sense. Thanks again though!

 

[Domenic]

... is there a way so that it takes the 30 days from the top to bottom...

Actually, the formula does indeed work this way...

 

[peterh2o]

Oh ok, I'll do some more testing. thanks!

 

[peterh2o]

I can't seem to get the formula to work correctly on my production sheet but on my beta sheet it works perfect. On my production sheet the data is being filled from references to other tabs. Could this be a problem because my beta sheet is all text that I input to test the formula?

 

[Domenic]

Assuming that you've confirmed the formula with CONTROL+SHIFT+ENTER, make sure that the dates are being recognized as true date values. What does the following return?

=ISNUMBER(A2)

...where A2 contains a date within your date column.

 

[peterh2o]

Domenic-
I have confirmed the formula with ctrl+shift+enter. The dates are showing up as FALSE while the data points are coming up as TRUE.