Desktop productivity for business analysts and programmers

How to calculate mean in Summary Table for row where missing values present?

Reply
Occasional Contributor
Posts: 11

How to calculate mean in Summary Table for row where missing values present?

In the attached file are the data set for the period Jan-Jun 2012/2013 and Summary table made on the base of this data set.

The purpose is to find the average of the MTOW and  average daily number of flights  for these  6 months' period.

There is no problem when values of this parameters present for each month as for the MEDIUM Aicraft.

As for the average MTOW of HEAVY Aircraft - the months with missing  MTOW must be excluded from the calculation of the average and it is OK - mean in Summary table work this way.

To calculate the daily averages number of flights for  HEAVY Aircraft correctly , all 6 month, even with missing values, must be included - mean in Summary Table does not work this way - this is a problem !!!

Grand Advisor
Posts: 17,308

Re: How to calculate mean in Summary Table for row where missing values present?

NataljaK wrote:

To calculate the daily averages number of flights for  HEAVY Aircraft correctly , all 6 month, even with missing values, must be included - mean in Summary Table does not work this way - this is a problem !!!

What do you mean by  "Missing Values must be included"?  Should they be 0? Do you need a record for every month so your table comes out properly?

I see the summary tables, but not what your data looks like which is the key thing.

Occasional Contributor
Posts: 11

Re: How to calculate mean in Summary Table for row where missing values present?

Hi Reeza. Thank you for the promt reply.

There are all data for 6 month for the Medium aircraft in the  data  set- so the 'average daily flights' (number of flights) for the period 'Up-to 30June' are calculate correctly in Summary Table in this case.

As for the Heavy aircraft then there are data just for 1month in 2012 and 2 month in 2013 in the data set. The other months are presented by missing value in the Summary Table. The function MEAN takes in to the account just months with nonmissing values when calculates the 'average daily flights' for the period 'Up-to 30June' - the result is  not correct in this case. Actually the value for the other  month must be 0, you are right, but only  for row  ˇ average daily flightsˇ . Averages for MTOW (weight of aircraft) are calculated for the months with nonmissing data and it is right.

Of course, I could add the rows with other months to the data set, but I  think it is not the nice solution.  I would prefer to have some function like 'meanmiss' or 'meanall'  to solve this problem in the Summary table ,but I have not found anything like this. 

Could anybody propose your own approach  to solve this problem ?

Valued Guide
Posts: 2,111

Re: How to calculate mean in Summary Table for row where missing values present?

you could rewrite the mean function into it's component parts:

MOTW=sum(of jan--june)/(n(of jan--june) + nmiss(of jan-june));

This would only be missing if all the flight entries are missing and count the other missing values as 0's.

Ask a Question
Discussion stats
  • 3 replies
  • 1084 views
  • 0 likes
  • 3 in conversation