+ Reply to Thread
Results 1 to 5 of 5

Replace positive numbers in array with zero

  1. #1
    Registered User
    Join Date
    05-28-2019
    Location
    Treviso, Italy
    MS-Off Ver
    2016
    Posts
    29

    Replace positive numbers in array with zero

    Good evening,

    Is there a "smart" way to substitute positive numbers in an array with zeroes?

    So instead of an array like ={3,2,1,0,-1,-2} you would get ={0,0,0,0,-1,-2}

    I managed to get it with an IF cycle but it would make the general formula extremely messy

    thanks for the help

  2. #2
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Replace positive numbers in array with zero

    Using a to refer to your array, you could use either IF(a<0,a,0) or INDEX((a<0)*a,0), noting that 0s in a might as well be included with positive numbers.

  3. #3
    Registered User
    Join Date
    05-28-2019
    Location
    Treviso, Italy
    MS-Off Ver
    2016
    Posts
    29

    Re: Replace positive numbers in array with zero

    The problem, if that is how I could call it, is that "a" is a very long formula and repeating it twice would make the overall formula very messy.
    Isn't there a leaner way? Sorry for asking again but I'm trying to get better at this kind of logical solutions!

  4. #4
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Replace positive numbers in array with zero

    Quote Originally Posted by zanchin View Post
    . . . "a" is a very long formula and repeating it twice would make the overall formula very messy. Isn't there a leaner way? . . .
    If you have a very long function which returns numeric values, and you want the positive values to be replaced by 0s, you have exactly 3 alternatives. I'll assume your long array formula is in cells C5:H5.

    1. Since your Excel version has the FORMULATEXT function, it'd only take entering the following formula in a blank cell,
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    copying that cell, selecting C5:H5, pasting special as values, with C5:H5 still selected, pressing [F2] then [Ctrl]+[Alt]+[Enter] to enter the formula-generated formula as an array formula.

    2. Use another range to come up with the final result. The final values would be INDEX((C5:H5<0),C5:H5,0).

    3. Post your long formula because there may be something in it which could allow for a more elegant solution. Without details from you, expect only generalities from others.
    Last edited by hrlngrv; 11-16-2020 at 06:06 PM. Reason: added parentheses around 2nd FORMULATEXT in #1

  5. #5
    Registered User
    Join Date
    05-28-2019
    Location
    Treviso, Italy
    MS-Off Ver
    2016
    Posts
    29

    Re: Replace positive numbers in array with zero

    Tomorrow, as soon as I get onto my work PC, I'll upload the excel file
    It's a contort formula and I don't expect you all to wrap your heads around it (I barely remember how I did it)
    I see, unfortunately, that I need to repeat the array twice to get the formula to work, which is a bummer!

    Thank you anyway, I'll post again tomorrow!

+ 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] time series return positive and negative numbers and numbers following
    By thedrinkerparadox in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-13-2020, 02:23 PM
  2. How to return the last positive group of numbers in array?
    By QuantEdge in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-22-2018, 12:17 AM
  3. Convert negative numbers to positive numbers using Excel "Find and Replace" Function
    By FoodieJoan In LA in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-05-2015, 09:40 PM
  4. I need answer to only show positive numbers, not minus numbers or zeros
    By stevedork in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-24-2013, 01:18 PM
  5. Sequences of positive and negative numbers in array
    By papayagirl in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2013, 08:42 PM
  6. Convert negative to positive in sheet containing both positive/negative numbers
    By sa02000 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-29-2010, 07:52 PM
  7. adding positive and negative numbers all as positive
    By tomvh444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2009, 04:08 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