Is it possible to perform AVERAGEIF formulas in Quickbase?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have over 1000 records that contain a "total number of days" field.

I need to add a field that averages my "total number of days" field.

2 questions...

What would the formula look like?

My 1000 records are split between 2 different vendors [Site Aqc Vendor]

Is it possible to write a formula that takes one vendor and averages the "total number of days" field?

Thanks!

Photo of Bryan

Bryan

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Averages can easily be done if you have a Relationship. The ideal way is to set up a table for your vendors and a relationship where 1 vendor has many "records". Then in that Relationship setup there is a button to "Add Summary field. Once that Average is created, if you like you can do a lookup back down to the children ("Records") records.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,434 Points 20k badge 2x thumb
With script you can perform any calculation you want and pretty much place the results anywhere you want. This application has a hundred records consisting of a numeric field [Data] and a text field [Type] which takes on one of two values Alpha or Beta:

Metrics - List All Records
https://haversineconsulting.quickbase.com/db/bi8vnspwt?a=q&qid=1

If you view a record (see screenshot) you will see the normal view records report along with a section that is dynamically populated using script with three calculations:

  • Average of All Records
  • Average of Alpha Records
  • Average of Beta Records

These calculations are done with script injected into the page using the image onload technique. Although I am calculating simple averages there is no limitation on the calculations or the content that can be inserted into the page using this general technique. If the need existed you could solve a numericly intensive problem like orbital mechanics and show the results using a custom d3 visualization. See: https://github.com/mbostock/d3/wiki/Gallery

Regarding your original question, Excel's AVERAGEIF function averages only those cells within the specified range which additionally meets some specific criteria. The code I implemented uses the Underscore library and applies an average to those records within the original query which additionally meets the select criteria (eg [Type] = Alpha or Beta).

Here is the essential code that was used:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=327