Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Solved Thanks All ! -- Analysing Data

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have 35 columns of data & around 10000 rows.
    few of the columns are
    ID Name Sex Age Country Region Date Sales ...

    I would like to generate reports that will tell me e.g. Total number of sales persons in the organization is .. & average age is .. & .. males .. females & average sales are ..

    same for a particular country or region etc....

    same for a particular date/date range ...

    Think I am demanding too much, but is there any way for this ?

    Thanks in advance.
    Girish

    [ This Message was edited by: Girish on 2002-05-09 11:00 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    England, UK.
    Posts
    526
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can work all those things out with pivot tables, whether or not they present the information in the way you need is another matter.

    RET79

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,648
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Consider using Pivot Tables. You can do a Search with as keyword Pivot with as author Mark W.

    Aladin

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Los Angeles, CA
    Posts
    752
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Use a Pivot Table to analyze the data, in the 3rd step of the wizard. double click on the data being analyzed and choose either, count, sum, product, average to get the result you want.

  5. #5
    New Member
    Join Date
    May 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your help.
    1. I am using pivot table now, but I don't want to consider blank/0 cells for calculating average.

    2. How can I use a function like StdDev in pivot table ?

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    > ...I don't want to consider blank/0 cells
    > for calculating average.

    Create an additional column for a transformation of your value...

    =IF(A2,A2)

    ...which will return FALSE if corresponding cell in column A is empty or contains 0. FALSE is ignored by AVERAGE.

    > How can I use a function like StdDev in
    > pivot table ?

    Like any other PivotTable summary function.

  7. #7
    New Member
    Join Date
    May 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks a lot Mark, it worked ! It saved me a lot of time !

    How can I find the number of unique values in pivot table ?

    [ This Message was edited by: Girish on 2002-05-08 07:58 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 07:57, Girish wrote:
    Thanks a lot Mark, it worked ! It saved me a lot of time !

    How can I find the number of unique values in pivot table ?

    [ This Message was edited by: Girish on 2002-05-08 07:58 ]
    Unfortunately, there's no PivotTable equivalent to SQL's DISTINCT option. There are a number of work-arounds that involve the addition of a new column to the data set, but they have drawbacks. For example, consider the following data set...

    {"Field1","Field2"
    ;"Apples",0.5
    ;"Apples",0.5
    ;"Bananas",1
    ;"Oranges",0.25
    ;"Oranges",0.25
    ;"Oranges",0.25
    ;"Oranges",0.25}

    ...where 'Field2' is calculated using...

    =1/COUNTIF(A:A,A2)

    A PivotTable can then be created with 'Sum of Field2' in the DATA area that counts unique (distinct) fruit.

    Unfortunately, as the data dimensions increase complex conditional IFs are needed, and you have to anticipate how the field will be used in a given PivotTable.

    The ultimate solution would be to source your PivotTable from a SQL that returns distinct values. I've been "pushing" this use of Get External Data in some of my recent postings.


    [ This Message was edited by: Mark W. on 2002-05-08 08:57 ]

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It worked, Thanks again !
    One more question, in the pivot table I want to filter on country=USA but I don't want USA to be displayed in the table.
    If I try to hide the country field, it hides it but also removes the filter, how can I keep the filter ?

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-08 14:02, Girish wrote:
    It worked, Thanks again !
    One more question, in the pivot table I want to filter on country=USA but I don't want USA to be displayed in the table.
    If I try to hide the country field, it hides it but also removes the filter, how can I keep the filter ?
    Why don't you make 'country' a PAGE field?

Some videos you may like

User Tag List

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
  •