AVGIF or AVG(Collect)

Melanie Bryant

Hello

I'm trying to avg each program based on the dept. I've used both If and Collect resulting in an error. I'm not sure if it's because I'm referencing another sheet incorrectly (the table below). Any help would be appreciated.

Thank you

have a table with a set of value streams, then columns with

Antonio Figueroa

Good Morning

Are you familiar with =AVERAGEIF()

I would use this formula

Kelly Moore

Hey @Melanie Bryant

I'd be happy to help. What formula have you tried, and what error are you seeing? Also, you mentioned you are referencing another sheet. To verify I'm understanding correctly, your source sheet would look like the top half of the screenshot, and your destination sheet looks like the bottom portion? I'm somewhat confused by your statement and the screenshot, and whether your data is cross sheet referenced or if the destination cells and the source cells exist all a single sheet.

I will assume your Smartsheet source sheet looks like the top portion and is a different sheet. Because errors sometimes occur if non-numeric values occur in the data range, we will add an additional criteria to filter for numbers only. However, doing this means we are using more than one criteria . We will use AVG/Collect which can handle multiple criteria whereas AverageIF is limited to using only one criteria.

This is the formula that goes in the Destination sheet Program 1 column.

=AVG(COLLECT({Source Sheet Program 1},{Source Sheet Program 1},ISNUMBER(@cell), {Source Sheet Area Audited}, [Area Audited]@row))

Remember if this is a cross sheet formula, you cannot just copy and paste but you must create the cross sheet references using the blue link in the formula window. The {Source Sheet Program 1} range will be the entire Program 1 column in the source sheet. You will then need to create the same formula but substitute for Program 2, Program 3 etc, for each of the other columns. The only thing changing is the Program range

Does this formula work for you? Don't hesitate to ask any question

Kelly

Melanie Bryant

Hi @Kelly Moore

Thank you for the assistance. You are correct in your assumptions.

I never hit post comment. I used the pro desk. I was trying to look at the whole table instead of the column and it fixed my issue. I got additional training on both.

Thanks for your response!