+ Reply to Thread
Results 1 to 4 of 4

If a value of zero occurs before another value within a column return 1 otherwise 0.

  1. #1
    Registered User
    Join Date
    03-01-2020
    Location
    Australia
    MS-Off Ver
    MS 2016
    Posts
    3

    If a value of zero occurs before another value within a column return 1 otherwise 0.

    Hi All!

    Banging my head against a wall here and appreciate the help!
    I'm effectively looking to create a flagging system to account for false-positives (desired output from the formula represented in column 5).

    Effectively i want to look through column 2 (values) and return a 0 if within a column a 0 value appears before the minimum value of that row.

    i.e for row/index 3 to 6: all of these have values greater than 0, however they are lower than their respective minimum value. Then row 7 equals 0. So i want to return in column 5 a flag = 0 against rows 3 to 6.
    this is different for rows/index 14 to 22: where all these values are greater than 0 and less than their respective minimum value, however then row 23 has a value greater than its minimum value. i then want for 14 to 22 to return a flag =1


    col 1 col 2 col 3 col 4 col 5
    index Values Minimum Value Flag 1: is value > 0 flag 2: does a zero appear before corresponding minimum value

    1 0 20 0 0
    2 0 20 0 0
    3 4 20 1 0
    4 11 20 1 0
    5 17 20 1 0
    6 19 20 1 0
    7 0 20 0 0
    8 0 20 0 0
    9 0 20 0 0
    10 0 30 0 0
    11 0 30 0 0
    12 0 30 0 0
    13 0 30 0 0
    14 1 30 1 1
    15 4 30 1 1
    16 6 30 1 1
    17 7 30 1 1
    18 8 30 1 1
    19 14 30 1 1
    20 19 30 1 1
    21 25 30 1 1
    22 28 30 1 1
    23 34 30 1 0
    24 0 30 1 0

    Hope this makes sense and someone is able to help

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: If a value of zero occurs before another value within a column return 1 otherwise 0.

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Registered User
    Join Date
    03-01-2020
    Location
    Australia
    MS-Off Ver
    MS 2016
    Posts
    3

    Re: If a value of zero occurs before another value within a column return 1 otherwise 0.

    I have attached a spreadsheet i believe.

    You can see i have tried two flagging formulas to get to the desired output, however it is too computationally intensive with the dataset i have.
    So i am looking for a different approach to being able to get to the desired output
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,007

    Re: If a value of zero occurs before another value within a column return 1 otherwise 0.

    Removed by JT

+ 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: 11
    Last Post: 01-23-2017, 11:40 AM
  2. [SOLVED] How to sum the number of times a value occurs in a column?
    By bk01eeh in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-30-2015, 11:20 AM
  3. [SOLVED] See if a value from column B occurs in A1 and return corresponding C-value
    By boomcie in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2014, 03:46 AM
  4. [SOLVED] Lookup a value that occurs more than once in a column
    By Harribone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-24-2013, 08:03 AM
  5. [SOLVED] Find.range to return True if match occurs
    By nivoe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2012, 07:26 AM
  6. [SOLVED] Enter Value 2 Cells Above Another Value That Occurs Throughout Column A
    By iLookup(HELP!, here) in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2012, 02:18 PM
  7. [SOLVED] Search Range and Return # of times a Text String occurs
    By Schwartz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2012, 04:23 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