1 Reply Latest reply on Dec 7, 2011 2:29 PM by philmodjunk

    Help on averageif...



      Help on averageif...


      I have a table which has hourly weather data of different cities. From those data I want to get the average of the hours between 6 to 17. I wrote a formula on a different table as; if( hour > 6 and hour < 17 ; Average( degree ). The relationship between 2 table is the month. But it didn't work out. How can I do it?

        • 1. Re: Help on averageif...

          Aggregate functions such as Average, Sum, Count.... compute the average of values in  list of values. If there is only one field listed, Average ( degree ), then this is the same as replacing it with just the field named degree as it is taking the average of a single value.

          These functions work in three formats:

          Average ( RepeatingField ) // the average of all values stored in different repetitions of the field

          Average ( field1 ; field2 ; field3 )

          Average ( RelatedtableOccurrenceName::FieldName )  // the average of value in FieldName for all records related to the current record

          If won't work here as it on controls whether or not an average is computed not which values are included.

          If you are keeping this all in one field, not using a separate related table, try this:

          change your calculation to:

          if( hour > 6 and hour < 17 ; degree )

          This field will return degree if hour is in the specified range and be empty in all other cases.

          Now define a summary field to compute the Average of this calculation field.

          There are several variations possible, but that's the basic idea. If you perform a find for just records where hour is in the specified range, a summary field that computes the average of Degree may be used and you won't need the calculation field. This second approach would make it possible to to get averages for any time range you prefer. (Note: am assuming hour field stores a number and not a time.)