Working around #CIRCULAR REFERENCE error to use the same formula multiple times?

Michael Yohe

I have this formula:

=AVG(COLLECT(IIQ:IIQ, [SE Manager]:[SE Manager], "John Lennon", [Row Number]:[Row Number], @cell >= 27))

I would like to use it in multiple rows. However, I get a #CIRCULAR REFERENCE error when I do, because it is used in the same column it references, so including more than 1 instance of that formula in the same column gives an error (you're allowed only 1 #CIRCULAR REFERENCE error).

How can I work around this?

Notice that the whole formula needs to only count rows 27 and above.

Can I use some kind of helper column workaround?

Or perhaps changing IIQ:IIQ to somehow only target rows 27 and above?

Genevieve P.

Hi @Michael Yohe

Is there a reason you're placing the formula in one of these columns? You could potentially use a Sheet Summary field instead (see here).

Or, instead of using the Row Number column, you could specify the row numbers with the columns themselves (see: Create a Cell or Column Reference in a Formula).

Ex:

=AVG(COLLECT(IIQ1:IIQ27, [SE Manager]1:[SE Manager]27, "John Lennon")

Then as long as the formula is outside of this range, you shouldn't receive a Circular Reference error.

If this doesn't work for you, it would be helpful to see a screen capture of your sheet, but please block out sensitive data.

Cheers,

Genevieve