4 Replies Latest reply on Jul 19, 2016 2:45 AM by bastian

    Average according two conditions ("Average if..")

    MFalaschi

      Hi everyone!

       

      Ok so i've been trying to deal with this using functions, but it's definitely not the way to do it..  specially without a built in "SumIf" or "AverageIf" function.

       

      I'm not an advanced filemaker user and i've been trying to understand if there is a way of using relationships or custom functions, but could't get any answer by myself.

       

      What i have:

      I'm doing measurements of Brightness conditions at the work place every year. Of course there are a lot of measurements every time, so i have created a table to record them.

       

      That table, called "Brightness Measurements" has the fields as follows:

      - General Measurement ID  (wich groups all the measurements in on year and is related to another table that has other information like mesure instrument, whether conditions, date and time, etc.)

      - Measurement Year

      - Measurement Area (groups all the measurements on the same area, for example the kitchen, the office, maintenance, etc.)

      - Measurement Place (specific place in the building where the measurement was taken, links the data with a blue print)

      - Measurement Value (which has the value of each place i mesure)

      - Brightness Average (which is what i want to calculate)

      - Brightness Uniformity (comparison between each value with the average of that specific area to say if it's ok or not)

      - Other fields not related to this issue.

       

      What i need to achieve:

      I need to calculate the average of all the measurement values of the same area for the year that measure was made. And i need that value to be updated every time i enter a new record for the same area and year.

       

       

      To simplify:

       

      General Measurement IDMeasurement YearMeasurement AreaMeasurement PlaceMeasurement ValueBrightness AverageBrightness Uniformity
      WIESS_LUX_20152015KITCHENA150150 (average the KITCHEN in the year 2015)

      150 = 150

      OK

      WIESS_LUX_20152015KITCHENB100150 (average the KITCHEN in the year 2015)

      100 < 150

      NOT OK

      WIESS_LUX_20152015KITCHENC200150 (average the KITCHEN in the year 2015)

      200 > 150

      OK

      WIESS_LUX_20152015KITCHEND150150 (average the KITCHEN in the year 2015)

      150 = 150

      OK

      WIESS_LUX_20152015OFFICEE300283 (average the OFFICE in the year 2015)

      300 > 283

      OK

      WIESS_LUX_20152015OFFICEF250283 (average the OFFICE in the year 2015)

      250 < 283

      NOT OK

      WIESS_LUX_20152015OFFICEG300283 (average the OFFICE in the year 2015)

      300 > 283

      OK

      WIESS_LUX_20142014KITCHENA200175 (average the KITCHEN in the year 2014)

      200 > 175

      OK

      WIESS_LUX_20142014KITCHENB200175 (average the KITCHEN in the year 2014)

      200 > 175

      OK

      WIESS_LUX_20142014KITCHENC150175 (average the KITCHEN in the year 2014)

      150 < 175

      NOT OK

      WIESS_LUX_20142014KITCHEND150175 (average the KITCHEN in the year 2014)

      150 < 175

      NOT OK

      WIESS_LUX_20142014OFFICEE250266 (average the OFFICE in the year 2014)

      250 < 266

      NOT OK

      WIESS_LUX_20142014OFFICEF350266 (average the OFFICE in the year 2014)

      350 > 266

      OK

      WIESS_LUX_20142014OFFICEG200266 (average the OFFICE in the year 2014)

      200 < 266

      NOT OK

       

      Any suggestions !?!???

      Thanks in advanced!