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 than 3 hours on task.
Source: Tasks
Time tracking stats for tasks of the building super hero suit
| Completed On | Task | Hours Spent | Feature |
|---|---|---|---|
| 03 Jan 2018 10:13 AM | Create a steel helmet to protect head | 19 | Protective Suit |
| 20 Jan 2018 04:08 PM | Create a chest protection from steel | 45 | Protective Suit |
| 05 Feb 2018 11:34 AM | Ask agency to create fancy identity for new super hero | 30 | Design |
| 09 Feb 2018 02:11 PM | Install super hero logo on body and helmet | 2 | Protective Suit |
| 09 Feb 2018 02:11 PM | Put on gloves with nice color | 2 | Protective Suit |
| 12 Feb 2018 09:01 PM | Ask Hulk to push in head | 5 | Combat Testing |
| 01 Mar 2018 06:45 PM | Ask Hulk to push in chest | 5 | Combat Testing |
Chart AREA
X: WEEK([Completed On])
Y: AVGIF([Hours Spent], [Hours Spent] > 3)
| WEEK(Completed On) | AVGIF(Hours Spent, Hours Spent > 3) |
|---|---|
| 1–7 Jan 2018 | 19 |
| 15–21 Jan 2018 | 45 |
| 5–11 Feb 2018 | 30 |
| 12–18 Feb 2018 | 5 |
| 26 Feb–4 Mar 2018 | 5 |