average last 17 cells (variable)
 

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

--
Message posted via http://www.officekb.com

average last 17 cells (variable)
 

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


--
goober
------------------------------------------------------------------------
goober's Profile: http://www.excelforum.com/member.php...o&userid=19838
View this thread: http://www.excelforum.com/showthread...hreadid=484859