Averageifs Question

Randy Van Winkle

@Genevieve P Perfect, thanks so much!

Cidnie Hoang

My apologies for reviving a relatively old thread but I've found this thread after attempting to do something similar with my data but referencing the info from a separate sheet.

For example, I want to average the number of days taken to complete a specific work priority: Urgent, High, Medium, Low, etc. and both the "Days Taken" and "Priority" columns are located in a different sheet. I can average the "Days Taken" column no problem but when adding the "Work Status" criteria, it returned with an "UNPARSEABLE" error.

This formula worked (but it averaged all priorities which is not what I'm looking for): =AVG({Request Tracker Range 2})

This failed: AVERAGEIF({Request Tracker Range 1}, @row = "Urgent", {Request Tracker Range 2})

Range 1 = Priority column

Range 2 = Days Taken column

An example of the two columns I want to calculate in my reference sheet

I tried to do AVG(COLLECT... but I'm so new to creating formulas and don't know where to begin so any help to point me to the right direction is much appreciated.

Thank you in advance,

Cidnie

Paul Newcome

@Cidnie Hoang Change the @row to @cell.

Cidnie Hoang

@Paul Newcome Thank you Paul, that worked! :)

Paul Newcome

@Cidnie Hoang Happy to help. 👍️

Alex Hackford

Hi, I'm trying to find the average satisfaction score between two dates, when the associated country is 'united states'.

I've tried a couple different variations of this based on previous comments in this thread, but with the below formula I'm getting a 'DIVIDE BY ZERO'

=AVG(COLLECT({satisfaction score range}, {survey date range}, [Week Start]@row, {survey date range}, <=[Week End]@row, {country of survey}, "united states"))

Any help is greatly appreciated.

Paul Newcome

@Alex Hackford That particular error is saying that your COLLECT isn't pulling any rows that have matching data. Double check that all date columns are in fact set as date type and that all dates within those columns are actual dates as opposed to just text strings that look like dates. The next step would be to run a filter that basically mimics the COLLECT function to see what gets pulled. That will tell you if there is an issue with the formula or an issue with the data.

Stacey Carrasco

@Paul Newcome I think your formula post on 10/9/2020 is a start to my issue.

I'd like to figure out the Average of the Children from a drop down column with different text variables . I'm stumped whether this is even possible. Can you calculate an Average of children with the Collect formula?

These are my variables in the drop down list:

1-5 hours per week.

6-8 hours per week.

9-11 hours per week.

12-15 hours per week.

More than 15 hours per week.

Is something like this even possible?

I'm guess my question is what's the average answers provided on our survey in column...?

=AVG(COLLECT([column title]:[column title], OR(@cell = "1-5 hours per week.", @cell = "6-8 hours per week.", @cell = "9-11 hours per week.")))

Any ideas.....

Thanks

Paul Newcome

@Stacey Carrasco The AVG function only works with numbers. What you need to do is replicate how the AVG function works which is the "actual" number divided by the "total" number.

=COUNTIFS([Column name]:[Column Name], @cell = "1-5 hours per week.") / COUNT([Column Name]:[Column Name])

Shelby Wilder

@Paul Newcome @Genevieve P.

Sorry for the late question! But I am attempting to use a AVG(Collect for the following formula and I keep receiving INCORRECT ARGUMENT error.

This formula should translate to Average of all TensileW cells, with the selected Vendor and between two designated dates.

=AVG(COLLECT({TensileW}, {LT Vendor}, Vendor@row, {Date Received}, AND(@cell >= [Date Range Start]@row, @cell <= [Date Range End]@row)))

All of the references reference the same secondary sheet.

Genevieve P.

Hi @Shelby Wilder

Instead of using AND, try listing the date range again and the criteria after it, like so:

=AVG(COLLECT({TensileW}, {LT Vendor}, Vendor@row, {Date Received}, @cell >= [Date Range Start]@row, {Date Received}, @cell <= [Date Range End]@row))

Amanda PM

Hi @Genevieve P. and @Paul Newcome,

Hoping it isn't too late to tag onto this thread. I am trying to use the above mentioned formulas to calculate the average vendor score across a few columns for a specific vendor out of a list of vendors.

Is it possible to use the AVG, AVGif, or AVG(Collect) to find the averages of (4) columns (Customer Service, Timeliness, Accuracy, Responsiveness) for every row where the Vendor Name = ABC Company?

I tried, =AVERAGEIF({Intake Form Range 1}, @cell = "[ABC Company]", {Intake Form Range 2})

Intake Form Range 1= [Vendor Name] column

Intake Form Range 2= [Customer Service], [Timeliness], [Accuracy], [Responsiveness] columns

and received an Incorrect Argument Set error. Any ideas of what I am doing wrong?

Thanks,

Amanda

Genevieve P.

https://community.smartsheet.com/discussion/comment/328564#Comment_328564

Hi @Amanda PM

You won't be able to AVG in this way, by selecting 4 columns at once.

I would suggest creating one formula per-category:

=AVERAGEIF({Intake Form Range 1}, @cell = "ABC Company", {Intake Form Customer Service})

=AVERAGEIF({Intake Form Range 1}, @cell = "ABC Company", {Intake Form Timelines})

... etc

Then in your Metric sheet you can choose to create an overall average as well, if necessary. Note that I also removed your [square parentheses] from the "criteria".

Cheers,

Genevieve

Amanda PM

@Genevieve P. Thank you so much!! That worked great. One more question, there are a handful of questions where the person can select "N/A". Is there a way to average only the numerical responses (scale of 1-5) in each column and omit any N/A responses when averaging the column? Below is what I am testing out but receiving error messages again:

=AVERAGEIF({Intake Form Range 1}, @cell = "ABC Company", {Intake Form Customer Service}, @cell <> "N/A")

Thank you,

Amanda

Genevieve P.

Hi @Amanda PM

The AVG or AVERAGEIF function will only average numerical values in the column. This means that you can list it as the range to Average without needing to exclude the "N/A" cells!

Are you seeing an error or incorrect result with:

=AVERAGEIF({Intake Form Range 1}, @cell = "ABC Company", {Intake Form Customer Service}) ?

If you have multiple criteria, you would want to use AVG(COLLECT:

=AVG(COLLECT({Column to Avg}, {First Criteria Column}, "Criteria 1", {Second Criteria Column}, "Criteria 2"))

Cheers,

Genevieve