AVERAGEIF

Dakota Haeffner

I am trying to create a formula averaging the time it takes to complete a request from my completed work sheet and it is returning an invalid operation error. Here is the formula,

=AVERAGEIF({Completed - Duration}, {Completed - Parent ID}, ="TOP")

I only want to average the parent rows durations and I plan on making another with the <> for the children as well.

Any suggestions?

Jeff Reisman

@Dakota Haeffner

With AVERAGEIF, if your criteria is not a part of the range that you want to average, you need to list the criteria range and criteria first, then the range you want to average. Try this:

=AVERAGEIF({Completed - Parent ID}, ="TOP", {Completed - Duration})

If you just wanted to average the Completed - Duration where the duration is less than 100, you would only need to list the range to average and the criteria: =AVERAGEIF({Completed - Duration}, <100)

Note: for evaluating text values, the equal sign before the text value "TOP" isn't strictly necessary.

Jeff Reisman

@Dakota Haeffner

With AVERAGEIF, if your criteria is not a part of the range that you want to average, you need to list the criteria range and criteria first, then the range you want to average. Try this:

=AVERAGEIF({Completed - Parent ID}, ="TOP", {Completed - Duration})

If you just wanted to average the Completed - Duration where the duration is less than 100, you would only need to list the range to average and the criteria: =AVERAGEIF({Completed - Duration}, <100)

Note: for evaluating text values, the equal sign before the text value "TOP" isn't strictly necessary.

Dakota Haeffner

@Jeff Reisman

That did it thank you!

The helper field threw me for a loop on this one. Also thank you for the note about the equal sign, I didn't know that either!

Dakota Haeffner

@Jeff Reisman

Another note on this, after I updated the formula it worked, but it made all of my fields on this metrics sheet where I am using the {Request - Parent ID}, ="TOP" now display an Incorrect Argument. This has happened before and I can't get it fixed this time. :(

Jeff Reisman

https://community.smartsheet.com/discussion/comment/322069#Comment_322069

I couldn't imagine what one has to do with the other, unless the AVERAGEIF formula is in one of the ranges specified in the metric sheet formula.

This is the guide for that error message, Make sure all your ranges are the same size, and that your arguments are all there with no extras. If you're only seeing this in formulas with the {Request - Parent ID} range, edit that range in one of the formulas and make sure you're referencing the entire column.

#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

1. For functions that take two ranges: The range sizes don’t match for the function.
2. The function is missing an argument.
3. There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.

https://help.smartsheet.com/articles/2476176-formula-error-messages

Dakota Haeffner

@Jeff Reisman

It has fixed itself. I don't know why is happens but all it does is raise my cortisol lol. Thanks!

Jeff Reisman

@Dakota Haeffner

I hear you on that! I've spent a bunch of time trying to "fix" formulas that should be working, when all I had to do was reload the sheets I was working on. Now I do that FIRST!