+ Reply to Thread
Results 1 to 4 of 4

Looking into a range of data and counting number of columns before data is greater than 1

  1. 05-23-2014, 09:14 AM #1
    tompee29
    tompee29 is offline
    Registered User
    Join Date
    12-05-2012
    Location
    liverpool
    MS-Off Ver
    Excel 2007
    Posts
    67

    Looking into a range of data and counting number of columns before data is greater than 1

    Hi Guys,

    I need a formula that will look into a range of data and tell me whan the last time a value exceeded 0 (working backwards).
    So below the first row would return a value of 6, the next 5, the next 0, the next 1 and so on.... hopefully that makes sense.
    I can do it with an if formula but the amount of days it will be looking at will be too many, plus the range will keep grwoing as time passes.


    Fri Sat Sun Mon Tue Wed Thu Fri Sat
    2 2 2 0 0 0 0 0 0
    1 1 1 1 0 0 0 0 0
    1 1 1 1 0 0 0 1 1
    1 1 0 1 1 1 1 1 0
    0 0 0 1 1 1 1 1 1
    0 0 0 0 0 0 0 1 1
    1 1 1 1 1 1 1 1 1
    1 1 1 1 1 1 1 1 1
    5 11 7 4 0 0 0 0 0
    5 6 4 0 0 0 0 0 0
    8 11 0 6 6 0 0 0 0
    0 0 0 0 0 18 17 13 18

    Many thanks,
    Tom.
    Reply With QuoteRegister To Reply
  2. 05-23-2014, 09:21 AM #2
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Looking into a range of data and counting number of columns before data is greater tha

    Hi,

    =LOOKUP(2,1/(A2:I2>0),COLUMNS(A2:I2)-(COLUMN(A2:I2)-MIN(COLUMN(A2:I2))+1))

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  3. 05-23-2014, 09:22 AM #3
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking into a range of data and counting number of columns before data is greater tha

    Try this...

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    -----
    2
    2
    2
    2
    0
    0
    0
    0
    0
    0
    6
    3
    1
    1
    1
    1
    0
    0
    0
    0
    0
    5
    4
    1
    1
    1
    1
    0
    0
    0
    1
    1
    0
    5
    1
    1
    0
    1
    1
    1
    1
    1
    0
    1
    6
    0
    0
    0
    1
    1
    1
    1
    1
    1
    0
    7
    0
    0
    0
    0
    0
    0
    0
    1
    1
    0
    8
    1
    1
    1
    1
    1
    1
    1
    1
    1
    0
    9
    1
    1
    1
    1
    1
    1
    1
    1
    1
    0
    10
    5
    11
    7
    4
    0
    0
    0
    0
    0
    5
    11
    5
    6
    4
    0
    0
    0
    0
    0
    0
    6
    12
    8
    11
    0
    6
    6
    0
    0
    0
    0
    4
    13
    0
    0
    0
    0
    0
    18
    17
    13
    18
    0


    This array formula** entered in K2 and copied down:

    =COLUMNS(A2:I2)-MATCH(2,1/(A2:I2>0))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  4. 05-23-2014, 09:31 AM #4
    tompee29
    tompee29 is offline
    Registered User
    Join Date
    12-05-2012
    Location
    liverpool
    MS-Off Ver
    Excel 2007
    Posts
    67

    Re: Looking into a range of data and counting number of columns before data is greater tha

    Thanks!!!!!!!!!!!!!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy data of range C4:C100 If range Q4:Q100 is greater than 0(zero)
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-01-2014, 06:23 AM
  2. [SOLVED] Counting the number of times a time appears in a range of data
    By alexw1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-04-2013, 12:23 PM
  3. Help with counting a range of data in two columns
    By Jules269 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2013, 08:29 AM
  4. [SOLVED] For StDev function omit data in range from the calculation that is greater than a number
    By eherron in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2013, 02:25 PM
  5. [SOLVED] Copy range every X number of columns, move data to the right
    By sans in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-09-2013, 08:45 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1