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
Check out the Formula Handbook template!