+ Reply to Thread
Results 1 to 6 of 6

If a number in column turns negative...

  1. #1
    Registered User
    Join Date
    01-17-2015
    Location
    toronto
    MS-Off Ver
    office 365
    Posts
    1

    If a number in column turns negative...

    Hello community,

    I do a lot of inventory management and I want to display proper backorder numbers and inventory levels.

    For instance, a number in column A says -2, I want the column B to say -2 while switching the -2 in column A into a zero. The -2 in column A would be the inventory level and column B would be where I'd want to display backorders. The inventory level gets its input from 'total inventory level' minus 'sales of day' tabs. Is there a way to create this into an automated function?


    Thank you
    Last edited by nipommu; 01-17-2015 at 10:57 PM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: If a number in column turns negative...

    Not with a regular function/formula, but it would be possible with VBA
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    957

    Re: If a number in column turns negative...

    Well wait that depends... show me the formula as it is in Column A. If that is a formula reading from another source then you could do this with an IF...

    Wrap the entire formula in an IF(FORMULA<0,0,FORMULA) and in B do IF(FORMULA<0,FORMULA,0) but again... this depends on what the contents of A actually is.

    Cheers

    EDIT - To FDibbins point, if these are static figures or values entered into A and you would like a formula to overwrite them...VBA is your source of Solution... so please share what is in the A column... or a small sample of the workbook is best, leaves no room for guessing.
    -If you think you are done, Start over - ELeGault

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: If a number in column turns negative...

    Ele, I took it that the OP has a value in A1 that would then be shown in B1. A1 would then revert to 0, but B1 would maintain the original value

  5. #5
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    957

    Re: If a number in column turns negative...

    Yea I read a couple times over and thought that but this line...

    The inventory level gets its input from 'total inventory level' minus 'sales of day' tabs
    Kind of sounds like it may have a formula... so we need the OP to clear that up

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: If a number in column turns negative...

    I have been wrong before - could be again

+ 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 first negative number in column, return row number
    By slade8200 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-06-2014, 11:14 AM
  2. Carrying Over Negative number to next column as a positive number
    By Redraven in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-21-2014, 06:39 AM
  3. [SOLVED] Return when a colomn turns and stays negative
    By JanLP in forum Excel General
    Replies: 6
    Last Post: 06-27-2014, 04:45 AM
  4. [SOLVED] How To Set a MAX Value, But when Exceeded Turns Negative
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-04-2014, 10:07 PM
  5. Lookup largest number in a column treating negative number as postive number
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 08:09 AM

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