Solved Thanks All ! -- Analysing Data

Girish

New Member
Joined
May 2, 2002
Messages
14
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
> ...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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top