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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    @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

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!