Averaging multiple columns

[TMHill]

Hi all

I need to insert a simple average calculation into lots and lots of columns.

Is there a way of doing this that does not involve either:
1) right clicking every column and selecting "Average" from Calculations.
2) Going through slice and dice, clicking on every item, clicking calculator, then changing the calc from Sum to Average.

Surely there must be a built in way of copying the "Average" formula across, such that it references each new column?

Many thanks in advance

Tom

 

[blom0344]

Hi Tom,

The answer is negative and from a designers point of view (the guys who made BO in the first place)perhaps for a good reason.
In one row you can decide to do a sum on one column, a count on a dimension, a max on another measure. This versatility is probably of greater value than being able to apply a function on different objects in one action. Possibly the outcome of some researching what was most likely be of the greatest value to the user. It does tend to cause some major waiting, cause every time BO does a formatting in between . So, I usually switch to the structure view instead. (perhaps that's a useful hint?) T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[TMHill]

Thanks for your view T.Blom

It strikes me as odd, though. From a designers perspective, I can see what you're saying, but rather than make it central functionality, there's nothing architectural to stop there being a simple external function which could do a "fill-in" based on certain criteria - ie. Effectively hard code the formula into each column as if entering 'Average' the usual way.

It seems like a fairly easy thing to add, and I cannot believe I'm the first user for whom it would save a LARGE amount of time.

Hmm.

Tom

 

[blom0344]

Hello Tom,

There is nothing to prevent you from applying a function like average over a range if the object you are applying the function to is the same. In fact you have to, cause BO sees this as the body of a table or crosstab (click on a column and BO selects the entire range).

But if your table consists of independant calculated values the expression for calculating the average is different for each column (the action of taking the average is the same)

If I state a variable like "Sales_2002" and one that shows "Sales2001" the average expressions will be different:

1. = Avg(Sales_2002)
2. = Avg(Sales_2001)

This you cannot solve with a generic function, but perhaps someone can come up with something in VBA to solve it for you

T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com