AverageIFS equivalent

[TWE_Toll]

Hi,

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.

Tim

[Suvrutt_Gurjar]

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.

help.appsheet.com

TOTALHOURS()

Count of hours in Duration

help.appsheet.com

AVERAGE()

Arithmetic average of list items

help.appsheet.com

Column Types: Diving Deeper

Learn more about each column type you can have when structuring your columns.

[GreenFlux]

Great explanation, @Suvrutt_Gurjar! This is a quality post.