Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Table?? (Microsoft Office Excel 2007)

    Would I use a pivot table for this and how would I go about doing so... Have a sheet named "all Data" and would like to compare results in column N to other columns with parameters. The result in column N (say cell N2) needs to be "At Risk" or "Overweight" and three out of the 6 columns (say either P2, R2, T2, V2, X2, and Z2) needs to result in a "Below HZ" so three out of the six needs to have this result in it. I would like to just have a list of what rows met these criteria. Can this be done?? Or am I just dreaming?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table?? (Microsoft Office Excel 2007)

    Could you post an example file? I don't tknow if a pivot table is appropriate, but there may be other ways.

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table?? (Microsoft Office Excel 2007)

    Sure thing, here is what I'm looking at.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table?? (Microsoft Office Excel 2007)

    I wouldn't use a pivot table for this.
    Insert a new column to the right of the fall data (i.e. in column AA).
    Enter a caption in AA1, e.g. Fall Select, or whatever you prefer.
    Enter the following formula in AA2:
    <code>
    =AND(OR(N2="At Risk",N2="Overweight"),COUNTIF(O2:Z2,"Below HZ")>=3)
    </code>
    Fill down as far as needed.
    You already have activated AutoFilter.
    Select TRUE in the dropdown in A1 to select the rows that meet the criteria, or FALSE to select the rows that don't meet the criteria.
    You can do the same for the spring data.

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table?? (Microsoft Office Excel 2007)

    Hans,
    I think we are close on this, however I'm getting a True result for everything. Even the ones that do not meet the criteria of "Overweight" and "At Risk" etc. I would like the formula to ignore the "Normal" results and not count them or at least give me a false value. Thanks for the help on this.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Pivot Table?? (Microsoft Office Excel 2007)

    I am a little confused, In the example you posted, with Hans' formula, there is only 1 TRUE (in row 14).

    Which ones in your example give TRUE which should be FALSE?

    [Do you calculations set to MANUAL? Try presssing <f9> to recalculate affter copying the formula]


    Steve

  7. #7
    Lounger
    Join Date
    Jan 2004
    Location
    Rochester, New Hampshire, USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Table?? (Microsoft Office Excel 2007)

    Ok the first time I tried it all true values, second time I copied and pasted it works. Go figure. Thanks for the formula!! Truly appreciated.

Posting Permissions

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