Averaging time (number value) within a column of a data table

If i have a column of numbers, how do i average all the values of each row within that column in my workflow? basically finding the mean/average of the values

i have attached what my data table looks like

Hi @Elisa_Cui,

The easiest way currently would be to use the Function Node. However, it can also be done with the Loop Node as well.

For both, you can query the Data Table using the Data Table: Get Node.

I’m happy to answer or explain more :slight_smile:

Yes! please explain more. I understand that i will be retrieving the values from my data table with the data table get node. but what do i do after? and how do i use to loop to add up all the values and then average?


So, just to give you some context, we have a feature ticket to support this in the Array Node. I just +1’d that for you.

For now, as I mentioned, the Function Node and the Loop Node are the best way to accomplish this.

In the Function Node, you would have to write JavaScript to do this:

payload.working = {}

var tableItems = payload.working.result.items // this should be the payload path to your table items

var total = 0;
for(var i = 0; i < tableItems.length; i++) {
    total += tableItems['COLUMN_NAME']; // update COLUMN_NAME to the name of the column

payload.working.average = total / tableItems.length;

However, I would not suggest the route above if you’re not familiar with JavaScript.

Using the Loop and Math Node, you can average numbers as well. However, I’d highly recommend reading the Loop Node Documentation.

Also, I’d recommend checking out the Workflow Lab. It’s a great tool to pick up things in the Workflow Engine:

Can you explain how I would use the loop node to calc the average?

In the Loop Source Path, you will configure the path to the items in your Data Table. In the screenshot below, I’m using data.body.numbers, which is just an array of numbers [80, 77, 88, 95, 68]. And, you can specify a path to access the “current item” of the array as your looping.

Then, you can use the Math Node to add the values to a variable that represents the sum. This would allow you to keep adding to the sum as you’re looping.

After looping through the items and building up a sum, you can use a Math Node outside of the loop to get the average:

{{working.result}} / {{data.body.numbers}}

Also, I’d highly recommend reading the Loop Node Documentation. It explains in-depth on how to use the Loop Node.

How do i turn my column of numbers into an array? i tried looping through the table and it did not work

I keep getting null as an outcome. I first get the rows of the data table i want and i loop through the items of that data table but the items have other columns and i only need one column. what is the “working.result” in the first math function? is it suppose to be empty

I am recieving null everytime, not sure what im doing wrong because i have the loop set to the items in the data table but the data table has multiple columns and i specified it to be the {{working.result}} +

Try this. Before your loop, you can initialize working.result to be the value of 0 using the Mutate Node.

Thank you! That did the trick. Appreciate it a lot!!