+ Reply to Thread
Results 1 to 7 of 7

First number from right greater than zero

  1. #1
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    848

    First number from right greater than zero

    Hi there,

    I have a table with list of amounts for each month.
    I am trying to get the value from the last, most recent month (first from the right) which is not zero.
    My formula works mostly, but returns incorrect figures where multiple sets of zero gaps are.

    Please refer to the attached spreadsheet:
    The formula is in column 'O'.
    Basically I need the amount in the most recent month which is not zero.
    I marked the incorrect amounts returned via red font color. Expected correct figures in column 'R'.

    Column 'P' has a variation of the same formula, hence of course an incorrect result too.
    Here I am after the average of the last 2 months before we have zero amounts. Expected correct figures in column 'S'.

    Any help, suggestions would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: First number from right greater than zero

    Try this instead, put in O2 and copy down

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

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: First number from right greater than zero

    How about
    =LOOKUP(2,1/(B2:M2>0),B2:M2)

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: First number from right greater than zero

    Another one
    Last Amount O2
    =1/LOOKUP(2,1/B2:M2)

    Last 2 Average P2
    =AVERAGE(1/LOOKUP(2,1/B2:M2),INDEX(B2:M2,MAX(1,MATCH(2,INDEX(1/B2:M2,))-1)))

    or
    =AVERAGE(1/LOOKUP(2,1/B2:M2),LOOKUP(2,1/B2:M2,A2:L2))
    Last edited by Bo_Ry; 06-01-2020 at 09:15 AM.

  5. #5
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    848

    Re: First number from right greater than zero

    Thanks Arkadi, Fluff13, and Bo_Ry
    That works perfect to find the last number in my real data.
    Much appreciated

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: First number from right greater than zero

    You're welcome & thanks for the feedback.

  7. #7
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    848

    Re: First number from right greater than zero

    Thanks also for the shortened formula for the averaging

+ 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. Replies: 2
    Last Post: 10-04-2015, 05:04 PM
  2. [SOLVED] Count number of cells in range since last number greater than zero
    By whitfieldcraig in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2015, 06:38 AM
  3. [SOLVED] Outputting number of times if a number is equal to or greater than
    By mnarvaez99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-11-2014, 10:51 AM
  4. Match Column A's Project number to change based on greater number in new row
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2013, 10:18 AM
  5. [SOLVED] How to get the number of times exactly 4 consecutive cells have a number greater than 6?
    By llane5150 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-29-2013, 11:23 PM
  6. [SOLVED] Condition and function to write number value greater than a specific number
    By excelinexcel7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-08-2012, 06:09 PM
  7. Replies: 3
    Last Post: 07-07-2006, 09:10 PM

Tags for this Thread

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