AVERAGEIF
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?
Best Answer
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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!
-
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. :(
-
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:
- For functions that take two ranges: The range sizes don’t match for the function.
- The function is missing an argument.
- There is an extra function in the argument.
Resolution
Correct the range size or arguments, adding or removing arguments in the formula.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
It has fixed itself. I don't know why is happens but all it does is raise my cortisol lol. Thanks!
-
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!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!