On Tue, 10 Jun 2008 23:56:03 -0700, Eric
wrote:

Thanks Ron. I want to add the 2 lowest values of the last 4 values in a row.
I am trying to work out a handicap for a player. The "-" entry means the
player did not play that game.
The result I get is zero and it causes a circular reference. The cause of
the circular reference is because it is including itself (the column function
is returning a 6). However, I don't know if the 0 result is caused by the
circular reference. I have pasted the data table again below with the formula
I have used. I entered it as an array (control+shift+enter).
A B C D E F
18 22 18 26 22 =SUM(SMALL(INDIRECT(ADDRESS(ROWS(1:1),LARGE(ISNUMB ER(1:1)*COLUMN(1:1),{1,2,3,4}))),{1,2}))
20 - - - 20 0
13 17 21 20 15 0
19 - 16 26 26 0

No, the formula will not work if it is part of a circular reference (in the row
being calculated).

If you want to have the formula on the same row as the data, then define a NAME
"rng" (without the quote marks) that refers to your Range of cells in a single
row using mixed addressing. E.g.

Name: rng
Refers to: =Sheet1!$A1:$F1

Then enter this **array** formula on the same row but in a column that is not
within rng.


=SUM(SMALL(INDIRECT(ADDRESS(ROW(),LARGE(ISNUMBER(r ng)*COLUMN(rng),{1,2,3,4}))),{1,2}))

Note that this formula will only work if it is on the SAME row as the data to
which it refers, which you implied above will be the case.

If not, it would be a matter of adjusting the ROW parameter, but we would need
to discuss what happens to the formula with ROW insertion. (That shouldn't be
a problem with this formula when placed on the same line as the data).
--ron