|
What formula to use
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 formula to use
|
What formula to use
On Tue, 10 Jun 2008 06:16:00 -0700, Eric
wrote: 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 |
What formula to use
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 Your help is greatly appreciated. "Ron Rosenfeld" wrote: On Tue, 10 Jun 2008 06:16:00 -0700, Eric wrote: 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 |
What formula to use
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 |
What formula to use
Thanks Ron.
It worked like a charm. "Ron Rosenfeld" wrote: 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 |
What formula to use
On Wed, 11 Jun 2008 06:08:02 -0700, Eric
wrote: Thanks Ron. It worked like a charm. Glad to help. Thanks for the feedback. --ron |
| All times are GMT +1. The time now is 10:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com