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]

Try the following...

1) Select an empty cell

2) Edit > Copy

3) Select the range of cells containing the date

4) Edit > Paste Special > Add > Ok

Does this help?

 

[peterh2o]

Success, thanks for all the help Domenic, much appreciated for your time.

 

[peterh2o]

Domenic- check your PM

 

[peterh2o]

Rather than doing averages and max's I just need to list the data without those dates that I want to exclude. It's set up like this:

1/24/2007 4.95
1/23/2007 4.92
1/22/2007 4.57
1/21/2007 4.23

In column C I want to list the data from column B but I want to exclude 1/22/2007 for example.

 

[Domenic]

Just to clarify, do you only want the data from Column B or do you want both Column A and Column B?

 

[peterh2o]

I basically want the original formula you gave me to give me data from column B into Column C but to exclude those certain days that I have preset.

 

[Domenic]

Assumptions:

A2:A5 contains the date

B2:B5 contains the corresponding value

J2:J10 contains a list of dates to exclude

D2 contains the number of days to query (In this example, we'll use 3 since the data is limited.)

Formulas:

E2:

=SMALL(IF(ISNA(MATCH($A$2:$A$5,$J$2:$J$10,0)),ROW($A$2:$A$5)),D2)

...confirmed with CONTROL+SHIFT+ENTER

F2, copied down:

=IF(ROWS($F$2:F2)<=$D$2,INDEX($B$2:$B$5,SMALL(IF(ROW($B$2:$B$5)<=$E$2,IF(ISNA(MATCH($A$2:$A$5,$J$2:$J$10,0)),ROW($B$2:$B$5)-ROW($B$2)+1)),ROWS($F$2:F2))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[peterh2o]

Hey Domenic, the formula doesn't need to query a specific amount of days. I'm looking to just relist a set of data so that it excludes certain data from certain days.

 

[Domenic]

In that case, based on the same assumptions, where A2:B5 contains the data, and J2:J10 contains the dates to exclude, try...

D2:

=SUMPRODUCT(--ISNA(MATCH(A2:A5,J2:J10,0)))

E2, copied down:

=IF(ROWS($E$2:E2)<=$D$2,INDEX($B$2:$B$5,SMALL(IF(ISNA(MATCH($A$2:$A$5,$J$2:$J$10,0)),ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E2))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[peterh2o]

Great, but I'm getting a #NUM starting on cell E38. Here's my formula:

=IF(ROWS($E$2:E2)<=$D$2,INDEX($B$2:$B$40,SMALL(IF(ISNA(MATCH($A$2:$A$40,$J$2:$J$14,0)),ROW($B$2:$B$40)-ROW($B$2)+1),ROWS($E$2:E2))),"")