AverageIFS equivalent


I have a column called [Gate to Gate Time] which subtracts [Finish]-[Start]. Type is set to “duration” as per appsheet requirements.

I have a virtual column called [Gate to Gate Average] which i need to return the average [Gate to Gate Time] if the date is today() and the [Gate to Gate Time] cell is not empty (Average time not including 0).

I have tried the average functions i can find in the forums however due to the [Gate to Gate Time] being set to type “duration” it is returning and error. If i change the type “duration” to “time” i get an error with the [Finish]-[Start].

Thanks for your help.


Hi ,
Please explore the below mentioned workaround.

You may wish to modify the [Gate to Gate Time] column’s expression to TOTALHOURS([Finish]-[Start])

With this modified expression you should be able to apply AVERAGE() on a list of [Gate to Gate Time] column duration values, now converted to decimal base.

Things to note:

  1. AVERAGE() works on numeric values’ columns such as number, decimal, price , percent
  2. AVERAGE() does not seem to work on temporal types of columns sych Date, DateTime, Time, Duration because I believe those columns do not have decimal arithmatic base. ( 60 seconds, 60 minutes, 24 hours)
  3. TOTALHOURS() converts the duration to decimal base.
  4. However by wrapping the time difference expression with TOTALHOURS() gives the time and duration in decimal format. So a duration of 3:30:00 becomes 3.5 , 4:15:00 becomes 4.25 and so on.

Great explanation, @Suvrutt_Gurjar! This is a quality post. :+1: :100: