Error Checking in Excel
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
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 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

Some videos you may like

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
  •