Combining AVERAGEIF and AND Functions

Options
✭✭
edited 03/14/23

Hi All,

I am wondering whether it's possible to combine the AVERAGEIF and AND functions together to meet 2 criteria.

In my case, I'm averaging time - {X} is in minutes hence the division by 60 to convert to hours and the 2 to round to two decimal points.

The below works as expected.

=ROUND(AVERAGEIF({X}, {X} > 0) / 60, 2)

However to add a second criteria - in my case, if {X} is greater than 0 AND checkbox @ that particular row = 1

I'm not sure if it's possible but possibly it would look something like this:

=ROUND(AVERAGEIF(AND({X}, {X} > 0,{Checkbox}=1)) / 60, 2)

I appreciate this is wrong but not clear on whether it's wrong or just not possible.

Tags:

• ✭✭✭✭✭✭
Options

Hi Alex,

=ROUND((AVG(COLLECT({X}, {X}, >0, {Checkbox}, 1))) / 60, 2)

Let me know if it works!

Best,

Heather

• ✭✭✭✭✭✭
Options

Hi Alex,

=ROUND((AVG(COLLECT({X}, {X}, >0, {Checkbox}, 1))) / 60, 2)

Let me know if it works!

Best,

Heather

• ✭✭
Options

Perfect, thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!