Finding last value in a row
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Finding last value in a row

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    What formula will return the the first value to the right?

    For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
    What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1)

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please excuse my typo. The question should be: "What forumla will return the first value to the LEFT?"

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-13 14:25, rambollet wrote:
    What formula will return the the first value to the right?

    For example: A1=empty cell, B1=3, C1=empty cell, D1=0, E1=5, F1=empty cell.
    What does the formula need to be in G1 to return the value of the first cell to the left than contains 0 or a value, or in this case 5 (E1)
    =INDEX(A1:F1,MATCH(9.99999999999999E+307,A1:F1))

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks.

    Plug in your formula and got the results I needed. Now I just need to spend some time figuring out the formula so I can understand it.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Brisbane, Down Under
    Posts
    542
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin, this is the second time you've posted a similar reply and not being a mathematician what is 9.9(al 14 of them)+307 represent?

    SamS

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-13 20:03, SamS wrote:
    Aladin, this is the second time you've posted a similar reply and not being a mathematician what is 9.9(al 14 of them)+307 represent?

    SamS
    Sam & Rambollet,

    9.99999999999999E+307 is the biggest positive number that Excel knows.

    When MATCH (with match type 1) fails to find for a numeric lookup value a matching numeric value in a row or column range to which it's applied, it returns the row (or column) number of the last cell in the range which houses a numeric value.

    Given this behavior of MATCH, we feed it that huge number as lookup value for which, failing to find a mathing value, the function is bound to return the row/column number of the last cell of numeric type.

    Thus, the formula exploits the fact that there will be practically no numeric value as big as 9.9...E+307 in the range tested.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-13 23:12 ]

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-13 23:12, Aladin Akyurek wrote:
    .... it returns the row (or column) number of the last cell in the range which houses a numeric value.
    More precisely (I think): it doesn't return the row- or columnnumber, but the value of the last cell in the range wich houses a numeric value.
    BTW Aladin: your knowledge is admirable!!

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-14 00:03, Albert 1 wrote:
    On 2002-03-13 23:12, Aladin Akyurek wrote:
    .... it returns the row (or column) number of the last cell in the range which houses a numeric value.
    More precisely (I think): it doesn't return the row- or columnnumber, but the value of the last cell in the range wich houses a numeric value.
    BTW Aladin: your knowledge is admirable!!
    Thanks Albert. MATCH does really return the row/column number of the last numeric cell. When that's fed to INDEX, you get the value the last numeric cell houses. I think you had the latter in mind.

    Aladin

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    390
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-14 01:05, Aladin Akyurek wrote:
    Thanks Albert. MATCH does really return the row/column number of the last numeric cell. When that's fed to INDEX, you get the value the last numeric cell houses. I think you had the latter in mind.
    You are right Aladin!
    Thank you for the rectification.

  10. #10
    New Member
    Join Date
    Feb 2014
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Finding last value in a row

      
    Rehashing an old thread here, but can this formula be altered to find the last text value in a range (similar to the cell references provided above)?
    Thanks!
    CG

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com