AnsweredAssumed Answered

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

Question asked by MFalaschi on Dec 22, 2015
Latest reply on Jul 19, 2016 by bastian

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!

Outcomes