Can i have the AVG(collect) formula in the same column as the range that I want to Average?

sonam.bang

I want to average the % complete column. I am writing the formula i the top row of this column and it doesn't allow me to do it. also, doesn't allow me to select any column from the same sheet. Am I missing something here? How can I solve this?

Paul Newcome

What are your other range/criteria sets to be used in the COLLECT function?

sonam.bang

I need the % complete column as range and the sprint column as the criteria with the criteria as "PI - 3 - Features"

Paul Newcome

Are you trying to get an average of the entire column, or are you trying to average the rows that are indented underneath?

sonam.bang

average of the entire column where sprint is "PI - 3 - Features"

Paul Newcome

=AVG(COLLECT([% Complete]:[% Complete], Sprint:Sprint, "PI - 3 - Features")

This would be the formula to use for that, but it will only work in one row of the [% Complete] column. If you try to enter it into a second cell of that column, it will generate a #CIRCULAR REFERENCE error.

Based on your screenshots and what it seems like you are trying to accomplish, you may want to consider using this instead:

=AVG(CHILDREN())

sonam.bang

Thanks. The first one works because i want to only average if its PI - 3 - Features.

I know its a very simple thing to do, i could not get it to work somehow ¯\_(ツ)_/¯

Thank you so much.

Paul Newcome

Happy to help. Just be mindful of where you put the formula. Based on your screenshot you are putting it in the [% Complete] column. If you were going to do this in the header row for each of the different sprints (the black rows), then you would want to use the CHILDREN reference instead of referencing the entire column.

sonam.bang

Yes. I added a helper row where i added this formula. I am not using the children reference because sometimes, my PI - 3 features spill over and go to another sprint. And i still want to be able to calculate that.

My struggle was to write the formula in the same column, it would let me select that same column as the range. But now i know the trick. Thanks to you.

Paul Newcome

Happy to help. Smartsheet made an update recently where you are allowed a single "circular reference" in a column, but there are still some rules surrounding it. You have to be referencing the entire column, and you can only do it once in the column. If you select a range other than a column (row or group of cells even if they are in the same column) that overlaps the cell containing the formula, you will get the error. If you try to put a second formula referencing the entire column in that same column, it will generate the error.

It also reads the cell as empty, so the output doesn't take into account the output (if that makes sense).

There is a thread somewhere out here in the Community where some of us took some collective notes on it. If you are interested, I would be happy to look for it and provide a link.