Count the average number of days based on criteria from another column

Column A - "CO Type" and only has two options - Fixed or T&M

Column B - "Average Days for Approval" and is calculated from two other columns - " Date Submitted" and " Date Approved"

I need a formula the will average the number of days for approval from Column B and for all the Fixed and T&M rows in Column A.

Help!

Find more posts tagged with

Accepted answers

Paul Newcome

Use an AVG/COLLECT combo instead.

=AVG(COLLECT([Days to App]:[Days to App], [CO Type]:[CO Type], @cell = "Fixed"))

Paul Newcome

Your formula has two different range sizes which is why that particular error is being thrown.

Your first range is rows 19-23, but your second range is 18-23. Make sure these two match. Does it work then?

All comments

kowal

hi @DavidM282667,

I would use helping formula column to calculate how many days are between Date Submitted and Date Approved.

put the formula inside like = [date approved]@row - [date submited]@row you will get a number here.

now when you have those numbers use the formula AVG formula like = =AVG([Helping Column]:[Helping Column])

DavidM282667

Thanks for the reply. I can get the average number of days no problem. What I need is the average number of days sorted by either "Fixed" or "T&M" from another column. I want to know what the average number of days for approval is for Fixed and T&M. Columns "CO Type" and "Days to App" from below screen shot

Hope you can help! Thanks for looking at this

Paul Newcome

Use an AVG/COLLECT combo instead.

=AVG(COLLECT([Days to App]:[Days to App], [CO Type]:[CO Type], @cell = "Fixed"))

DavidM282667

=AVG(COLLECT([Days to App]19:[Days to App]23, [CO Type]18:[CO Type]23, @cell = "Fixed"))

This returns a #incorrect argument set error.

DavidM282667

I want all the Fixed items and the average of their time to approval and all the T&M items and their average time to approvals.

Thanks for the help

Paul Newcome

Your formula has two different range sizes which is why that particular error is being thrown.

Your first range is rows 19-23, but your second range is 18-23. Make sure these two match. Does it work then?

DavidM282667

Excellent! Thank you for the help and patience with my novice SS skills. That works

Paul Newcome

Happy to help. 👍️

DavidM282667

So one more wrinkle - I need to exclude rows with zeros (0) as its throwing off the average. A zero means approval has not been received yet.

Paul Newcome

In that case you would ad another range/criteria set inside of the COLLECT function.

[Number Column Name]:[Number Column Name], @cell <> 0

DavidM282667

=AVG(COLLECT([Days to App]29:[Days to App]44, @cell <> 0, [CO Type]29:[CO Type]44, @cell = "Fixed"))

What do I have wrong? Its not working.

Paul Newcome

You forgot the range part. The first range is the range to average. Then you follow that up with range, criteria, range, criteria. In this instance, you need to duplicate that first range.

DavidM282667

Thanks again - this is like learning a different language.

That works

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.