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!

 

[peterh2o]

Disregard column B, Column D is where I want the data from Column C to populate into. Column C is getting its data from Columns R-U. I want to exclude A11, A21 and A26. I hope this clarifies things.

 

[Domenic]

One further clarification...

Do you want each valid entry listed next to their corresponding values in Column A and B, or do you want to list them in order and without blanks?

 

[peterh2o]

I'd just like the data from C to be relisted in D excluding a list of holidays. So to answer your question, I'd like them in reverse chronological order without blanks.

 

[Domenic]

Try...

D3:

=SUMPRODUCT(--(A3:A31<>""),--ISNA(MATCH(A3:A31,J3:J14,0)))

E3, copied down:

=IF(ROWS($E$3:E3)<=$D$3,INDEX($C$3:$C$31,SMALL(IF($A$3:$A$31<>"",IF(ISNA(MATCH($A$3:$A$31,$J$3:$J$14,0)),ROW($C$3:$C$31)-ROW($C$3)+1)),ROWS($E$3:E3))),"")

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Does this return the desired result?

 

[peterh2o]

Perfection, thanks Domenic.

 

[peterh2o]

I've got a another question that is somewhat along the same lines of my previous questions but much simpler...I think.
I have a sample of 7 days and I only need 3 of the most recent dates to be displayed, but it also needs to take into account holidays so that it doesn't list them. I tried fiddling with the previous formulas, but haven't had much luck.

 

[Domenic]

If I undertand correctly, simply replace the formula in D3 with the number 3. This number can be changed as desired. So, for example, if you'd like to display the corresponding values for the 10 most recent dates, enter 10 instead of 3.

If you actually want the dates displayed instead of their corresponding values, change...

INDEX($C$3:$C$31

to

INDEX($A$3:$A$31

If you'd like both the dates and corresponding values displayed, post back.

Hope this helps!