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]

Could you also tell me what the logic behind the SUMPRODUCT formula and how it relates to the other formula. thanks.

 

[Domenic]

Does A2:A40 contain blanks? If so, try the following formula instead for D2...

=SUMPRODUCT(--(A2:A40<>""),--ISNA(MATCH(A2:A40,J2:J14,0)))

Hope this helps!

 

[peterh2o]

A2:A40 has the dates. B2:B40 contains the data and J2:J14 contains the holidays which should be excluded.

 

[Domenic]

Yes, but does A2:A40 have any cells which are blank? If so, try the last formula I offered and see if it helps...

 

[peterh2o]

It works, I didn't input the correct cells. Thanks again for all the help Domenic.

 

[peterh2o]

I'm running into a similar problem like I did yesterday where it is not dumping the dates in my production sheet. I changed the dates by doing the same function of adding 0 but I'm thinking that the problem lies in the data set. The data set is pulling through an IF statement so for example in cell C3 has the data points the formula is:
=IF(R3="",IF(S3="",IF(T3="",IF(U3="",IF(V3="",W3,V3),T3),S3)R3)

 

[peterh2o]

bump

 

[Domenic]

...The data set is pulling through an IF statement so for example in cell C3 has the data points the formula is:
=IF(R3="",IF(S3="",IF(T3="",IF(U3="",IF(V3="",W3,V3),T3),S3)R3)

How does C3 come into play? Do you mean that the values in what has been Column B up to now contains the above formula? By the way, the above formula is not correct -- probably a typo.

 

[peterh2o]

Column C contains the data that I want to pull into Column D. Column A has the dates. What I'm doing is tracking 6 items where the first is the newest and only has 15 days worth of data and then from there it jumps to the next one that has 30 days and so on. I'm tracking the data going back 90 days. So that's where the formula I posted is coming from. I hope this clears things up.

 

[Domenic]

Still unclear. Can you provide...

1) a small sample of the data, and

2) the actual/expected results, based on the sample data