Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first and last entry in a column
In a column (array $B$7:$B$54) I have entered all numbers 1 from B12:B24. I
would like a formula which recognises the first entry (on B12) and returns a value from A12. Also I would like a formula which recognises the last entry of the entries (onB24) and returns the value from A24. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first and last entry in a column
=INDEX(A7:A54,MIN(IF(B7:B54<"",ROW(B7:B54)))-MIN(ROW(B7:B54))+1)
=INDEX(A7:A54,MAX(IF(B7:B54<"",ROW(B7:B54)))-MIN(ROW(B7:B54))+1) both are array formulae, so commit with Ctrl-Shift-Enter, not just Enter -- --- HTH Bob (change the xxxx to gmail if mailing direct) "JohnUK" wrote in message ... In a column (array $B$7:$B$54) I have entered all numbers 1 from B12:B24. I would like a formula which recognises the first entry (on B12) and returns a value from A12. Also I would like a formula which recognises the last entry of the entries (onB24) and returns the value from A24. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding the first and last entry in a column
In a column (array $B$7:$B$54) I have entered all numbers 1 from B12:B24.
If I understand: For the first entry: =INDEX(A7:A54,MATCH(1,B7:B54,0)) For the last entry: =INDEX(A7:A54,MATCH(2,B7:B54)) Or: =INDEX(A7:A54,MATCH(10^10,B7:B54)) Biff "JohnUK" wrote in message ... In a column (array $B$7:$B$54) I have entered all numbers 1 from B12:B24. I would like a formula which recognises the first entry (on B12) and returns a value from A12. Also I would like a formula which recognises the last entry of the entries (onB24) and returns the value from A24. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|