How to average non-adjacent cells

Ldonoghue ✭✭
edited 06/20/22 in Formulas and Functions

I'm trying to average non-adjacent ranges. I've tried =AVERAGEIF([Range:Range],[Range:Range],"<>0"), but get an error each time.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference?

  • It's a really big sheet, so I don't think screenshots will work. But as an example, say my columns are A - Z. I want to calculate an average for A:E and J:M, where the # in any cell does not equal zero. I tried following the cell range references on the formula guide: [A]@row:E@row,separating them with a comma, but this resulted in the information after the comma being read as criteria rather than range. So I tried a semicolon instead ([A@row:E@row;[J@row:M@row,"<>0") - but the result is a syntax error. Thanks!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try somethign like this:

    =AVG(AVG(COLLECT(A@row:E@row, A@row:E@row, @cell <> 0)), AVG(COLLECT(J@row:M@row, J@row:M@row, @cell <> 0)))

    Basically you would use an AVG/COLLECT combo for each range and use those outputs as the entries in a regular AVG function.

  • Thank you - I will experiment with this!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

  • jcouncil
    jcouncil ✭✭✭✭
    edited 06/01/23

    I tried following this suggestion for a similar need, but am getting #incorrect argument error. Any suggestions for modification?

    =AVG([Column38]@row, AVG(COLLECT([Nonverbal - Eye Contact]@row:[Nonverbal - Poise]@row), AVG(COLLECT([Verbal - Enthusiasm]@row:[Verbal - Elocution/Speech]@row), AVG(COLLECT([Interaction - Introduction/Agenda/Objective]@row:[Interaction - Product Knowledge]@row)))))

    Where column 38 is a one cell non averaged set of data, and the rest of the cells are nonadjacent multi cell ranges.

    Thank you!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jcouncil

    The COLLECT portion there is identifying criteria (e.g. "not 0") however in your case it looks like you're just trying to average multiple cells, is that correct?

    If so, this is how you would structure that formula:

    =AVG([Column38]@row, AVG([Nonverbal - Eye Contact]@row:[Nonverbal - Poise]@row), AVG([Verbal - Enthusiasm]@row:[Verbal - Elocution/Speech]@row), AVG([Interaction - Introduction/Agenda/Objective]@row:[Interaction - Product Knowledge]@row))

    Note that this will weight your averages. For example, if [Column38] has a 1, then [Nonverbal - Eye Contact] and [Nonverbal - Poise] both have 10, instead of an average of 7, you'll get an average of 5.5.

    This is because it first takes the average of any of your ranges ([Nonverbal - Eye Contact]:[Nonverbal - Poise] = 10), then it takes that number to average it with [Column38] (10 & 1) instead of looking at all three values separately and then taking an average together (10, 10, 1).

    What I would actually suggest doing here is to SUM all your data pieces together, then divide it by the number of cells you're looking at:

    =SUM([Column38]@row, SUM([Nonverbal - Eye Contact]@row:[Nonverbal - Poise]@row), SUM([Verbal - Enthusiasm]@row:[Verbal - Elocution/Speech]@row), SUM([Interaction - Introduction/Agenda/Objective]@row:[Interaction - Product Knowledge]@row)) / number

    Does that make sense?



  • jcouncil
    jcouncil ✭✭✭✭

    @Genevieve P. Yes! I started with sum and was trying to figure out how to average afterwards with the average function, but this is so much simpler! THANK YOU :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!