average last 17 cells (variable)

[brian thompson3001 via OfficeKB.com]

using formula in A2 =average(IT:Index(A1:IT1,sumproduct(large(column(A:IT)*
(A1:IT1<>""),A4))))
A4 is variable cell for calculation. However, I require result to be in row
1. Keep getting curcular ref. Require result in a3
/
Can anyone assist?

brian(at )thompson3001.fsnet.co.uk
Regards

Brian

 

[goober]

The circular refference meant that you are using a formula that
references the cell it is in. i.e.

In cell A1 is the formula =SUM(A1:A25)
This will give you a circular reference error.

To avoid this the cell with the formula being calculated must not
reference its self.
In cell A1 is the formula =SUM(A2:A25)
This will not have the error.

Having Cell A2 =average(IT:Index(A3:IT1,sumproduct(large(column(A
:IT)*(A3:IT1<>""),A4))))
Will stop the circular reference