AVGIF

AVGIF([Price] + ([Price] * [Tax Percent])/100, [Price] > 0)

Syntax

AVGIF(numerical expression, logical expression)

Aggregation function returns the conditional avg of values calculated using numerical expression.

numerical expression: Basic arithmetic calculation created using the math operators +, -, *, /, numerical data fields and constants.

logical expression: An expression that produces a Boolean value when evaluated, i.e. one of TRUE or FALSE. The combination of data fields, constants, text, numeric and date expressions with logical operators >,<,>=, <=, != and etc. can be used to create logical expressions

Use cases

Spent time on tasks

Compare average spent hours for completed tasks per week. Spent hours are included when it was spent more then 3 hours on task.

Source: Tasks

Time tracking stats for tasks of the building super hero suit

Completed OnTaskHours SpentFeature
03 Jan 2018 10:13 AMCreate a steel helmet to protect head19Protective Suit
20 Jan 2018 04:08 PMCreate a chest protection from steel45Protective Suit
05 Feb 2018 11:34 AMAsk agency to create fancy identity for new super hero30Design
09 Feb 2018 02:11 PMInstall super hero logo on body and helmet2Protective Suit
09 Feb 2018 02:11 PMPut on gloves with nice color2Protective Suit
12 Feb 2018 09:01 PMAsk Hulk to push in head5Combat Testing
01 Mar 2018 06:45 PMAsk Hulk to push in chest5Combat Testing

Chart AREA

X: WEEK([Completed On])

Y: AVGIF([Hours Spent], [Hours Spent] > 3)

Chart

WEEK(Completed On)AVGIF(Hours Spent, Hours Spent > 3)
1–7 Jan 201819
15–21 Jan 201845
5–11 Feb 201830
12–18 Feb 20185
26 Feb–4 Mar 20185