Average of 3 columns

building reports desktopintelligence
1

hi

all

I have 3 coloumns in my report and they are coming out 3 differnt data providers and some records have null values in them .I need to calculate average of those 3 coloumns and here is the catch if there any null values in any coloumn it should not consider it , meaning it should give the average remaing two columns only.

can any body help me oput in this.

Thanks
pavan.

dpavank2007 (BOB member since 2007-05-24)

2

3 variables
1: Add the three columns’ values.
2: Count the number of non null column (something like

=(If IsNull(<Margin>) Then 0 Else 1) + (If IsNull(<Quantity sold>) Then 0 Else 1 )+(If IsNull(<Sales revenue>) Then 0 Else 1)

3: Divide the total by the count.

You could do it all in one variable, but it’s probably easier to use 3

pablolee :uk: (BOB member since 2008-07-29)