Solved Thanks All ! -- Analysing Data

[Girish]

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 ]

[RET79]

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

[Aladin Akyurek]

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

Aladin

[Parra]

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.

[Girish]

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 ?

[Mark W.]

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

[Girish]

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 ]

[Mark W.]

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 ]

[Girish]

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 ?

[Mark W.]

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?