Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

How to exclude certain data points

This is a discussion on How to exclude certain data points within the Excel Questions forums, part of the Question Forums category; I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be ...

  1. #1
    New Member
    Join Date
    Jan 2007
    Posts
    33

    Default How to exclude certain data points

    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!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,632

    Default

    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!

  3. #3
    New Member
    Join Date
    Jan 2007
    Posts
    33

    Default

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

  4. #4
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,632

    Default

    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...

  5. #5
    New Member
    Join Date
    Jan 2007
    Posts
    33

    Default

    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!

  6. #6
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,632

    Default

    ... is there a way so that it takes the 30 days from the top to bottom...
    Actually, the formula does indeed work this way...

  7. #7
    New Member
    Join Date
    Jan 2007
    Posts
    33

    Default

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

  8. #8
    New Member
    Join Date
    Jan 2007
    Posts
    33

    Default

    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?

  9. #9
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    13,632

    Default

    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.
    Domenic
    Microsoft MVP - Excel
    xl-central.com - "For Your Microsoft Excel Solutions"

  10. #10
    New Member
    Join Date
    Jan 2007
    Posts
    33

    Default

    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.

Page 1 of 4 123 ... LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com