+ Reply to Thread
Results 1 to 17 of 17

Find positive cells

  1. #1
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Find positive cells

    Hi

    In the attached sheet I have given scenarios where a formula should return a result based on whether a cell has a positive value.

    The formula is to first find the last cell in a range and return a 1 if it is a positive value, it then looks at the next cell before it and retun 2 if that cell is also positive and so on....

    If the last cell is negative then it return 0 if the cell before it is positive it should still return 0.

    If the last cell is positive and the cell before it is negative and the next cell before it is positive the formula should only return 1

    This is explained more clearly on the spreadsheet.

    Thank you for your interest.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    04-01-2015
    Location
    The Netherlands
    MS-Off Ver
    2003/2007/2010/2016/office 365
    Posts
    880

    Re: Find positive cells

    If the file is representative then

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    03-22-2020
    Location
    Malaysia
    MS-Off Ver
    2013 and 365 Pro Plus
    Posts
    56

    Re: Find positive cells

    MarvinP have the solution
    Last edited by biex; 04-05-2020 at 02:54 PM.

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find positive cells

    Hi gotroots,

    I have a little different if formula of:
    =IF(I6<0,0,IF(H6<0,1,IF(G6<0,2,3)))
    See the attached with some random numbers below yours and the answer in Yellow to pull down.
    Find positive cells at end of 3.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  5. #5
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Find positive cells

    Hi everyone and thanks!

    MarvinP, your solution with attached sample solved the problem.

    Take care and keep safe all!!

  6. #6
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Find positive cells

    Hi again,

    I forgot to mention the solution needs to be dynamic to allow for the range to be extended. New data will be added daily so the formula needs to take account of that.

    Thanks

  7. #7
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find positive cells

    Hi gotroots,

    If the data will extend daily, I suggest you look at Dynamic Named Ranges in your formulas. I don't think you need it in my sample but just so you know about them.
    https://www.ozgrid.com/Excel/DynamicRanges.htm

  8. #8
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Find positive cells

    Hi MarvinP,

    I have looked at using Dynamic Named Ranges as you suggest but from what I can tell it will not be suitable for a worksheet where there is more that one unique row of data. I have literally hundreds of rows of data with each row being unique and having no relation to any other row of data.

    Perhaps what I really need is a formula that will find the last cell in a row, In this way I can freely add data to each row as needed.

  9. #9
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find positive cells

    Hey gotroots,

    To find the last cell in a row you do the last argument in the Dynamic Named Range like this:

    =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

    The COUNTA(Sheet1!$1:$1) part tells the Excel to go over to the last column in Row 1.

    Does this make sense? Offset can do both rows and columns. Read more at:
    https://exceljet.net/excel-functions...ffset-function

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Find positive cells

    Here's another approach.

    In G11:K15 of the attached this formula returns random negative/positive numbers.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In F11 and filled down this returns the count of right most positive numbers in each row.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Dave

  11. #11
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Find positive cells

    Perfect FlameRetired

    Returning the count for each row is what is needed.

    Thankyou.
    Last edited by AliGW; 04-07-2020 at 09:42 AM. Reason: Please don't quote unnecessarily!

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Find positive cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  13. #13
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Find positive cells

    Just one other thing, I only need a maximum of three cells from the end included in the result. The sample spreadsheet would give a count of 5 if all the cells in a given row had a positive value, I need the count to max out with a count of only 3. Apart from that the formula is brilliant.

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Find positive cells

    So wrap it in a MIN function:

    =MIN(3,your_formula)

  15. #15
    Registered User
    Join Date
    04-27-2019
    Location
    Northern Ireland
    MS-Off Ver
    2019
    Posts
    62

    Re: Find positive cells

    Wrap! That's proving a bit beyond me AliGW

    Not able to incorporate MIN into the formula.

    =IFERROR((MATCH(2,1/($G11:$XFD11>0))-MATCH(2,1/($G11:$XFD11<0)))*(INDEX($G11:$XFD11,MATCH(25^25,$G11:$XFD11))>0),COUNTIF($G11:$XFD11,">0"))

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Find positive cells

    Like this?

    =MIN(3,IFERROR((MATCH(2,1/($G11:$XFD11>0))-MATCH(2,1/($G11:$XFD11<0)))*(INDEX($G11:$XFD11,MATCH(25^25,$G11:$XFD11))>0),COUNTIF($G11:$XFD11,">0")))

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Find positive cells

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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] Find last value in column if a positive number
    By nickpavlov in forum Excel General
    Replies: 2
    Last Post: 05-24-2019, 08:15 PM
  2. [SOLVED] Find percentage of positive numbers from A4:A23?
    By Mr.Castle in forum Excel General
    Replies: 8
    Last Post: 08-23-2018, 03:57 PM
  3. [SOLVED] Find pattern (positive, negative, positive)
    By tsakta13ole in forum Excel General
    Replies: 6
    Last Post: 06-01-2016, 05:10 AM
  4. Macro To Find Positive Value In One Cell Then Copy Value Of Corresponding Cells
    By markrennolds in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2009, 06:50 PM
  5. Find negative value and apply to next positive value
    By jamphan in forum Excel General
    Replies: 5
    Last Post: 07-16-2008, 04:52 PM
  6. [SOLVED] FIND POSITIVE VALUE
    By FIRSTROUNDKO via OfficeKB.com in forum Excel General
    Replies: 2
    Last Post: 03-22-2006, 09:45 PM
  7. Find first positive value in row 3 and return date from row 1
    By rlaw68 in forum Excel Formulas & Functions
    Replies: 31
    Last Post: 09-06-2005, 06:05 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