Hello,
I wonder what are the exact rules defined for calculating an aggregate function like "SUM" in a pivot table, when a complex formula calculated field is involved.
For example, let us assume we have a table with three fields: (item, quantity, value) .
In a pivot table you could insert the aggregate data Sum(quantity) or Sum(value) that have clear meanings: the total quantity or the total value.
Now the name "calculated field" is a bit misleading.
Assume we defined the quantity cost = value/quantity.
When aggregating, in a pivot table, excel will calculate:
Sum(cost) = Sum(quantity)/Sum(value)
As far as I remember, this rule has always been exactly what I really wanted. Really a luck!
However, it is a bit misleading to call "cost" a "calculated field". Since if "cost" is a field like other field, like "quantity" for example, then the rule would be
Sum(cost) = Sum(quantity/value)
which is generaly something different.
Now the quiz: what happens for more complicated expressions, what is the rule, and why would the rule be also generally lucky???
For example, let us assume we have these fields now: (item, a, b, c, d, e).
How would excel aggregate in a pivot table the following function f = (a*a+b*b)/(a*b+d*c)?
For example, would it be calculated as follows:
Sum(f) = (Sum(a)*Sum(a)+Sum(b)*Sum(b))/(Sum(a)*Sum(b)+Sum(d)*Sum(c))
and -if yes- would that be justified for most applications, and why?
I think this question goes back to what are basically pivottables or aggregate function.
I know in practice, but not in general.
My motivation is that I come to an application where I will automatically append "calculated fields" to my pivot tables in order to match what is done in a another application and defined by the user. I would like to know the rules in order to understand what could happen in more complex situation and explain to the users -as clearly as possible- what the rules are.
Thanks for you help.
I wonder what are the exact rules defined for calculating an aggregate function like "SUM" in a pivot table, when a complex formula calculated field is involved.
For example, let us assume we have a table with three fields: (item, quantity, value) .
In a pivot table you could insert the aggregate data Sum(quantity) or Sum(value) that have clear meanings: the total quantity or the total value.
Now the name "calculated field" is a bit misleading.
Assume we defined the quantity cost = value/quantity.
When aggregating, in a pivot table, excel will calculate:
Sum(cost) = Sum(quantity)/Sum(value)
As far as I remember, this rule has always been exactly what I really wanted. Really a luck!
However, it is a bit misleading to call "cost" a "calculated field". Since if "cost" is a field like other field, like "quantity" for example, then the rule would be
Sum(cost) = Sum(quantity/value)
which is generaly something different.
Now the quiz: what happens for more complicated expressions, what is the rule, and why would the rule be also generally lucky???
For example, let us assume we have these fields now: (item, a, b, c, d, e).
How would excel aggregate in a pivot table the following function f = (a*a+b*b)/(a*b+d*c)?
For example, would it be calculated as follows:
Sum(f) = (Sum(a)*Sum(a)+Sum(b)*Sum(b))/(Sum(a)*Sum(b)+Sum(d)*Sum(c))
and -if yes- would that be justified for most applications, and why?
I think this question goes back to what are basically pivottables or aggregate function.
I know in practice, but not in general.
My motivation is that I come to an application where I will automatically append "calculated fields" to my pivot tables in order to match what is done in a another application and defined by the user. I would like to know the rules in order to understand what could happen in more complex situation and explain to the users -as clearly as possible- what the rules are.
Thanks for you help.
Last edited:
