+ Reply to Thread
Results 1 to 3 of 3

return numeric value from 5th cell back in row while ignoring cells with text or blank

  1. #1
    Registered User
    Join Date
    06-24-2017
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    2

    return numeric value from 5th cell back in row while ignoring cells with text or blank

    I would like to return the numeric value in the cell that is always 5th back from the last entry in a row (moving left to right) while ignoring cells that contain text or blanks. Assume my data is in the 6th row that starts in C6 and moves right with each new entry, the last cell used will be AA6. The formula I have been using is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This has served me well by returning the 5th value back while ignoring blank cells. However, I needed to add cells containing only text to the row. Now the formula counts the cells containing text and returns the text as the value when it is the 5th cell back. I would like it to ignore all cells containing text so they will not be included in the 5 count and will not be returned as a value. Currently if the very last cell (furthest right and first to be counted) contains text it seems to ignore and skip it, however if there are other cells containing text within the last 5 cells it counts those. The worksheet has many rows so once I have the formula for row 6 I will copy down to the bottom of the worksheet. Any solutions would be greatly appreciated.
    Thanks in advance for your time and wisdom.
    Last edited by jmo81; 07-26-2017 at 12:04 AM.

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: return numeric value from 5th cell back in row while ignoring cells with text or blank

    So you want to count back 5 NUMERIC cells, completely ignoring text, blanks, etc., correct?
    If yes, try this ARRAY FORMULA*:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    NOTE- I included this clause in case there were fewer than 5 numbers:
    ...MIN(5,COUNT($C6:$AA6))...

    *Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.


    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 07-25-2017 at 10:12 PM.

  3. #3
    Registered User
    Join Date
    06-24-2017
    Location
    U.S.
    MS-Off Ver
    2010
    Posts
    2

    Re: return numeric value from 5th cell back in row while ignoring cells with text or blank

    It worked perfectly, copied down with no problems.
    Thank you so much Lee for your fast reply and for your solution!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return last 4 values in a Row, ignoring blank cells
    By UnitedCloud01 in forum Excel General
    Replies: 6
    Last Post: 07-24-2017, 01:12 AM
  2. Return most common text from column cells with formula while ignoring blanks
    By rosco01995 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-29-2014, 03:55 AM
  3. [SOLVED] Sorting a text column while ignoring blank cells
    By mic2mic in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-30-2013, 04:58 AM
  4. Move next value to the desired cell in a row ignoring blank cells in between
    By saeed9313 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2012, 09:29 AM
  5. Replies: 12
    Last Post: 09-17-2012, 01:03 PM
  6. Replies: 4
    Last Post: 02-06-2006, 03:05 AM
  7. [SOLVED] Find a non-blank cell and bring back text a in same row
    By Rod in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 03-24-2005, 06:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1