Calculated field - pivottable
 

Hello



I have a table with data as follows





A B

Animal Age

1 Cow 14

2 Sheep 23

3 Cow 87

4 Cow 123

5 Sheep 345

6 Sheep 22

7 Cow 12

8 Sheep 91



It is very simplified of course. I want to have a pivottable and in that
pivottable I want to see how many cows and how many sheeps there are within
a <90 and 90 age band. In other words I want to produce something like
this:



Animal Age < 90 Age 90

Cow 3 1

Sheep 2 2

Total 5 3



Is it possible to do that using calculated field (or other features of
pivottable). I wouldn't like to add any extra columns in the source table to
calculate the age band.

Regards



Igor

Calculated field - pivottable
 

using function we can derive the results

try this

=COUNT(IF((A2:A10="cow")*(B2:B10<=90),)) ( use ctrl + shift + enter )

=COUNT(IF((A3:A10="cow")*(B3:B1090),)) ( use ctrl + shift + enter )



On Nov 27, 12:07*pm, "IgorM" wrote:
Hello

I have a table with data as follows

* * * * * * A * * * * * * * * * * B

* * * * * * Animal * * * * * *Age

1 * * * * *Cow * * * * * * * *14

2 * * * * *Sheep * * * * * * *23

3 * * * * *Cow * * * * * * * *87

4 * * * * *Cow * * * * * * * *123

5 * * * * *Sheep * * * * * * *345

6 * * * * *Sheep * * * * * * *22

7 * * * * *Cow * * * * * * * *12

8 * * * * *Sheep * * * * * * *91

It is very simplified of course. I want to have a pivottable and in that
pivottable I want to see how many cows and how many sheeps there are within
a <90 and 90 age band. In other words I want to produce something like
this:

Animal * * * * * *Age < 90 * * * * * * * * * *Age 90

Cow * * * * * * * *3 * * * * * * * * * * * * * * * * 1

Sheep * * * * * * *2 * * * * * * * * * * * * * * * * 2

Total * * * * * * * 5 * * * * * * * * * * * * * * * * 3

Is it possible to do that using calculated field (or other features of
pivottable). I wouldn't like to add any extra columns in the source table to
calculate the age band.

Regards

Igor

Calculated field - pivottable
 

Hi,

First, what are you feeding the animals, I want some of it - sheep living to
345!

you don't need a calculated field

1. Remove the blank rows in your data
2. Select the data and choose Data, PivotTable and PivotChart Report, Next,
Next, Existing Worksheet, D1 (or any empty cell you choose), Finish.
3. Drag the animal field to the Row area
4. Drag the age field to the Column area
5. Drag the animal field to the Data area
6. Select the age titles 12 to 87 in the column area and choose PivotTable,
Group and Show Detail, Group
7. Type over the name Group1 with a title like <91 years
8. Select the age titles from 91 to 345 and repeat step 6. Name this =91
Years.
9. Double-click the title <91 years (this will collapse it)
10. Double-click the title =91 years (this will also collapse
11. Right click the grand total in the column area and choose Hide.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"IgorM" wrote:

Hello



I have a table with data as follows





A B

Animal Age

1 Cow 14

2 Sheep 23

3 Cow 87

4 Cow 123

5 Sheep 345

6 Sheep 22

7 Cow 12

8 Sheep 91



It is very simplified of course. I want to have a pivottable and in that
pivottable I want to see how many cows and how many sheeps there are within
a <90 and 90 age band. In other words I want to produce something like
this:



Animal Age < 90 Age 90

Cow 3 1

Sheep 2 2

Total 5 3



Is it possible to do that using calculated field (or other features of
pivottable). I wouldn't like to add any extra columns in the source table to
calculate the age band.

Regards



Igor

Calculated field - pivottable
 

It's a top secret mixture :)

I like your solution, but there is only one issue. What when new data is
entered and it's not grouped yet. The user (correct me if I'm wrong) has to
group it again. Is there a way to that in a way that will automatically
group them when the table is refreshed, so there is no need for user to play
with the pivottable structure?

Kind regards

Igor

U¿ytkownik "Shane Devenshire"
napisa³ w wiadomo¶ci
...
Hi,

First, what are you feeding the animals, I want some of it - sheep living
to
345!

you don't need a calculated field

1. Remove the blank rows in your data
2. Select the data and choose Data, PivotTable and PivotChart Report,
Next,
Next, Existing Worksheet, D1 (or any empty cell you choose), Finish.
3. Drag the animal field to the Row area
4. Drag the age field to the Column area
5. Drag the animal field to the Data area
6. Select the age titles 12 to 87 in the column area and choose
PivotTable,
Group and Show Detail, Group
7. Type over the name Group1 with a title like <91 years
8. Select the age titles from 91 to 345 and repeat step 6. Name this =91
Years.
9. Double-click the title <91 years (this will collapse it)
10. Double-click the title =91 years (this will also collapse
11. Right click the grand total in the column area and choose Hide.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"IgorM" wrote:

Hello



I have a table with data as follows





A B

Animal Age

1 Cow 14

2 Sheep 23

3 Cow 87

4 Cow 123

5 Sheep 345

6 Sheep 22

7 Cow 12

8 Sheep 91



It is very simplified of course. I want to have a pivottable and in that
pivottable I want to see how many cows and how many sheeps there are
within
a <90 and 90 age band. In other words I want to produce something like
this:



Animal Age < 90 Age 90

Cow 3 1

Sheep 2 2

Total 5 3



Is it possible to do that using calculated field (or other features of
pivottable). I wouldn't like to add any extra columns in the source table
to
calculate the age band.

Regards



Igor