What formula to use

[Eric]

I am trying to find a formula to calculate the lowest 2 numbers of the last 4
in a row.
A row can be made up as follows:
A B C D E F
25 18 22 18 26 22
17 20 - - - 20
18 13 17 21 20 15
18 19 - 16 26 26
Any help would be appreciated.

 

[Herbert Seidenberg]

For advanced users:
http://www.savefile.com/files/1602247

 

[Ron Rosenfeld]

I am trying to find a formula to calculate the lowest 2 numbers of the last 4
in a row.
A row can be made up as follows:
A B C D E F
25 18 22 18 26 22
17 20 - - - 20
18 13 17 21 20 15
18 19 - 16 26 26
Any help would be appreciated.

What do you mean by "calculate the lowest 2 numbers"?

Do you want to ADD them; do you want to just return them?

Given your data can be anywhere in ROW 1, the smallest number of the last 4:

=MIN(TRANSPOSE(INDIRECT(ADDRESS(1,LARGE(ISNUMBER(1:1)*COLUMN(1:1),{1,2,3,4})))))

or

=SMALL(TRANSPOSE(INDIRECT(ADDRESS(1,LARGE(ISNUMBER(1:1)*COLUMN(1:1),{1,2,3,4})))),1)

and the second smallest:

=SMALL(TRANSPOSE(INDIRECT(ADDRESS(1,LARGE(ISNUMBER(1:1)*COLUMN(1:1),{1,2,3,4})))),2)

All of these have to be entered as an **array** formula. After copying or
pasting the formula into a cell, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

If you want to ADD the lowest two values of the last 4:

=SUM(SMALL(INDIRECT(ADDRESS(1,LARGE(ISNUMBER(1:1)*COLUMN(1:1),{1,2,3,4}))),{1,2}))

also **array-entered**

If you want to enter the formula in some cell, and fill down with the ROW
reference automatically adjusting, change the portion of the formulas:

....ADDRESS(1, ...

to

....ADDRESS(ROWS($1:1, ...

Let me know if this helps with what it is you are trying to do.


--ron

 

[Eric]

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
18 22 18 26 22 =SUM(SMALL(INDIRECT(ADDRESS(ROWS(1:1),LARGE(ISNUMBER(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

Your help is greatly appreciated.

 

[Ron Rosenfeld]

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(ISNUMBER(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(rng)*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

 

[Eric]

Thanks Ron.

It worked like a charm.

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(rng)*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

 

[Ron Rosenfeld]

Thanks Ron.

It worked like a charm.

Glad to help. Thanks for the feedback.
--ron