Averageifs Question

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 Thank you Paul, that worked! :)

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 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 @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.

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. 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