| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find the number of the lowest filled row
I'm building some complex sheets and to complete this task I need a formula
that finds "the number of the lowest row, of a column, witch is not empty". For exemple, in Column A we have: A1= 12.3 A2= 2.9 A3= 3.5 A4= (is empty) A5= 3 A6= 10.3 A7= (is empty) A8= (is empty) Ax= (is empty) .. . .. . (and so on) Under A6, there is no filled cells in the column A. I need a formula that returns "6", that is the number of the lowest row that is not empty (A6=10.3). PS: I managed to get some good results adding formulas to a parallel column (B) this way Column A Column B A1=12.3 B1=If(A10;Row();0) (B1 returns 1) A2=2.9 B2=If(A20;Row();0) (B2 returns 2) A3=3.5 B3=If(A30;Row();0) (B3 returns 3) A4= B4=If(A40;Row();0) (B4 returns 0) A5=3 B5=If(A50;Row();0) (B5 returns 5) A6=10.3 B6=If(A60;Row();0) (B6 returns 6) A7= B7=If(A70;Row();0) (B7 returns 0) A8= B8=If(A80;Row();0) (B8 returns 0) .. . .. . .. . And then the formula "Max(B:B)" returns the desired number (6). But this way is not good enough because it needs a "parallel column" (column B in my example) for EACH column I want to avaliate, and it would make my sheet confusing for its users. I need a solution so much... lol Hope someone can help... lol Roberto Villa Real |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the number of the lowest filled row
One way:
=MATCH(LOOKUP(99^99,A:A),A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roberto Villa Real" wrote in message ... I'm building some complex sheets and to complete this task I need a formula that finds "the number of the lowest row, of a column, witch is not empty". For exemple, in Column A we have: A1= 12.3 A2= 2.9 A3= 3.5 A4= (is empty) A5= 3 A6= 10.3 A7= (is empty) A8= (is empty) Ax= (is empty) . . . . (and so on) Under A6, there is no filled cells in the column A. I need a formula that returns "6", that is the number of the lowest row that is not empty (A6=10.3). PS: I managed to get some good results adding formulas to a parallel column (B) this way Column A Column B A1=12.3 B1=If(A10;Row();0) (B1 returns 1) A2=2.9 B2=If(A20;Row();0) (B2 returns 2) A3=3.5 B3=If(A30;Row();0) (B3 returns 3) A4= B4=If(A40;Row();0) (B4 returns 0) A5=3 B5=If(A50;Row();0) (B5 returns 5) A6=10.3 B6=If(A60;Row();0) (B6 returns 6) A7= B7=If(A70;Row();0) (B7 returns 0) A8= B8=If(A80;Row();0) (B8 returns 0) . . . . . . And then the formula "Max(B:B)" returns the desired number (6). But this way is not good enough because it needs a "parallel column" (column B in my example) for EACH column I want to avaliate, and it would make my sheet confusing for its users. I need a solution so much... lol Hope someone can help... lol Roberto Villa Real |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the number of the lowest filled row
Don't really need LOOKUP:
=MATCH(1E100,A:A) -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... One way: =MATCH(LOOKUP(99^99,A:A),A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roberto Villa Real" wrote in message ... I'm building some complex sheets and to complete this task I need a formula that finds "the number of the lowest row, of a column, witch is not empty". For exemple, in Column A we have: A1= 12.3 A2= 2.9 A3= 3.5 A4= (is empty) A5= 3 A6= 10.3 A7= (is empty) A8= (is empty) Ax= (is empty) . . . . (and so on) Under A6, there is no filled cells in the column A. I need a formula that returns "6", that is the number of the lowest row that is not empty (A6=10.3). PS: I managed to get some good results adding formulas to a parallel column (B) this way Column A Column B A1=12.3 B1=If(A10;Row();0) (B1 returns 1) A2=2.9 B2=If(A20;Row();0) (B2 returns 2) A3=3.5 B3=If(A30;Row();0) (B3 returns 3) A4= B4=If(A40;Row();0) (B4 returns 0) A5=3 B5=If(A50;Row();0) (B5 returns 5) A6=10.3 B6=If(A60;Row();0) (B6 returns 6) A7= B7=If(A70;Row();0) (B7 returns 0) A8= B8=If(A80;Row();0) (B8 returns 0) . . . . . . And then the formula "Max(B:B)" returns the desired number (6). But this way is not good enough because it needs a "parallel column" (column B in my example) for EACH column I want to avaliate, and it would make my sheet confusing for its users. I need a solution so much... lol Hope someone can help... lol Roberto Villa Real |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Find the number of the lowest filled row
Yep ... but I did say "One way".<bg
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "T. Valko" wrote in message ... Don't really need LOOKUP: =MATCH(1E100,A:A) -- Biff Microsoft Excel MVP "RagDyer" wrote in message ... One way: =MATCH(LOOKUP(99^99,A:A),A:A) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roberto Villa Real" wrote in message ... I'm building some complex sheets and to complete this task I need a formula that finds "the number of the lowest row, of a column, witch is not empty". For exemple, in Column A we have: A1= 12.3 A2= 2.9 A3= 3.5 A4= (is empty) A5= 3 A6= 10.3 A7= (is empty) A8= (is empty) Ax= (is empty) . . . . (and so on) Under A6, there is no filled cells in the column A. I need a formula that returns "6", that is the number of the lowest row that is not empty (A6=10.3). PS: I managed to get some good results adding formulas to a parallel column (B) this way Column A Column B A1=12.3 B1=If(A10;Row();0) (B1 returns 1) A2=2.9 B2=If(A20;Row();0) (B2 returns 2) A3=3.5 B3=If(A30;Row();0) (B3 returns 3) A4= B4=If(A40;Row();0) (B4 returns 0) A5=3 B5=If(A50;Row();0) (B5 returns 5) A6=10.3 B6=If(A60;Row();0) (B6 returns 6) A7= B7=If(A70;Row();0) (B7 returns 0) A8= B8=If(A80;Row();0) (B8 returns 0) . . . . . . And then the formula "Max(B:B)" returns the desired number (6). But this way is not good enough because it needs a "parallel column" (column B in my example) for EACH column I want to avaliate, and it would make my sheet confusing for its users. I need a solution so much... lol Hope someone can help... lol Roberto Villa Real |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find lowest number and return it's adjacent cell | Excel Worksheet Functions | |||
| How to find the lowest number of a row and delete it automatically | Excel Discussion (Misc queries) | |||
| How to find last filled in cell in a column? | Excel Worksheet Functions | |||
| find the lowest value in a row and add a number to it | Excel Worksheet Functions | |||
| how do i find the lowest number in a column in Excel? | Excel Discussion (Misc queries) | |||
Linear Mode
