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