4 Replies Latest reply on Feb 28, 2012 7:42 AM by MarcMcCall

# Average Miles Per Gallon

### Title

Average Miles Per Gallon

### Post

I have 7 calculation fields for miles per gallon on one table. Day 1 through 7.  I'm trying to figure out how to add those together and get an overall 7 day average.  I set it up like this Sum(MilesPerGallonDay1;MilesPerGallonDay2;MilesPerGallonDay3;MilesPerGallonDay4;MilesPerGallonDay5;MilesPerGallonDay6;MilesPerGallonDay7)/7  However, I would like to do it so it only calculates when there is a value in the other fields.  So if I have sat, sun, tue, it wont need to be /7 only /3 would I use a Case formula for this?

• ###### 1. Re: Average Miles Per Gallon

Why not just use the Average function, it automatically excludes empty fields from the divisor count. It works with fields within one record or multiple records in a portal. However, a zero (or other number) in the field will count in the divisor.

• ###### 2. Re: Average Miles Per Gallon

It only needs to work within individual records, I think I would rather use a calculation field.

• ###### 3. Re: Average Miles Per Gallon

It is a calculation, results number.

Average (MilesPerGallonDay1; MilesPerGallonDay2; MilesPerGallonDay3; MilesPerGallonDay4; MilesPerGallonDay5; MilesPerGallonDay6; MilesPerGallonDay7 )

If all 7 fields contain a number it will divide by 7, if only 5 fields have a data in them it will only divide by 5, etc.

Average is the function right above Case in "Show all by name"

• ###### 4. Re: Average Miles Per Gallon

Well, I was trying to mark Bumper's answer as the best one, but apparently my screen was still refreshing and chose mine, but Bumper's answer it the best one......