final try pivot help

  • Thread starter Thread starter boris
  • Start date Start date
B

boris

Okay, no one has come to this challenge yet, so please
help if you can.

In a PivotTable, I have two columns which appear as
fields. Let's say A and B. I need to take each record's
A divided by B, and then take the average of that. The
way I have tried is to do a calculated field with the
formula Average (A/B). The challenge I am trying to
overcome is Excel's insistence on using the Sum function
in presenting the results of a calculated field. In
other words, no matter what I base my calculation on,
Excel will show me my result as a sum. To combat this
(since the original thought was to calculate A/B, then
use field settings to indicate average instead of sum), I
have tried the above formula of Average (A/B). Excel
takes this to mean the Average of A divided by the
Average of B, which is not correct.

Anyone out there, PLEASE. I am desperate at this point.
 
Are you trying to find the average of all A/B's? Does
your number of rows change? If not, you could cheat and
just divide the sum of A/B by the total number of A/B
values, which would give you an average. If your total
number of values is constantly changing, you might be able
to have an autofill count the number of cells for you and
then use the last number in your formula. I'm probably not
any help, but thought it was worth a shot.
 
Why do you need a pivot table to do this? Can you not
just add another column to the source data? C=A/B, then
take the average of C?
 
I can, but 1) this is part of a larger effort I'm doing
and 2) I am trying to figure it out to avoid having to do
calculations which I thought the pivot table was supposed
to be able to do.

In other words, I thought one of the points of the pivot
was to be able to do calculations on data before
manipulating it. I certainly can make another column,
but hoped I wouldn't have to. I wanted a scenario where
someone could dump a bunch of rows in and have to do
nothing other than update the pivot (which is referenced
to an expanding section of rows that would include the
news ones). In this case, having to have someone go and
fill down the formulas I wrote in some columns would just
be a step I wanted to avoid.

I'll take it that there is no way to do this, then?

Anyone?
 
Back
Top