+ Reply to Thread
Results 1 to 7 of 7

Return when a colomn turns and stays negative

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Pila Poland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Return when a colomn turns and stays negative

    Hi.
    I have been here for some time, and have always been able to find someone with a similar case. But not this time - so now it is my time to hope for some expert help.

    I have a list of items supplied by supplier - sometimes they donīt send all and sometimes they send more - So I made an accumulate sum to see outstanding. So the value fluctuates from negative over zero to positive, and back again.
    I would like to return the column title from when an item goes negative and stays negative.
    Here is a picture of when I am trying to explain.

    1.gif

    I have in line row 48 a value of -228 in week 21 and a plus in week 22 - then it goes minus again week 23 and stays there until now. So I would like to return "Week 23" - not as I am only able to do - return the first negative (Week 21).
    It can go from negative to positive a lot of times - so somehow I have to include a IF function - stating that a total (until the end) has to still be negative.
    Colomn 159 that starts negative and turns positive - I have filtered out on my result page - so it is only showing if total is negative.

    But how to get formula in column H to return - not the first - but the first of the last negatives. Phew - hope I explain myself good enough

    I have also attached the excel file for any one willing to assist me in this.
    Thanks in advance - and please help me prove to my colleagues that excel are really able to do everything. (In this case just my skills are not sufficient)
    Jan LP
    Attached Files Attached Files
    Last edited by JanLP; 06-26-2014 at 06:17 AM. Reason: Wrong heading

  2. #2
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Return when a colomn turns and stays negative

    Hi there,
    do not have much time, and therefor posting not 100% formula:
    Copy formula below to H2 and drag down:
    Please Login or Register  to view this content.
    (Note that it is array formula and needs to be confirmed with Ctrl+Shift+Enter instead of enter)
    It is complex and deals also wit the case that all nubmers are positive or negative.
    But there is one issue if that line of numbers starts with ZERO.
    The logic is to find the position of last positive number, what is quite simple:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    , and return last positive number position+1.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Try to adjust if You want.
    Help from Experts.xls

    Hope it helps!
    Regards
    Miroslav R.

    (If You like my solutions, feel free to add reputation.)

  3. #3
    Registered User
    Join Date
    04-10-2013
    Location
    Pila Poland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Return when a colomn turns and stays negative

    Wauw.. This is soooo close Miroslav. Yes - the only deal breaker is if the line starts or contains a zero or empty - it looks like it is not counted towards finding last positive number (+1).
    Example

    Attachment 328079

    Any way to avoid this ? I tried to edit formula because it looks like your formula is not counting empty or zero cells - correct ?

    If I change cell to contain a positive number - it returns correctly.
    Attachment 328080

  4. #4
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Return when a colomn turns and stays negative

    Hi there,
    canīt open You attachements.
    Yes - it is not taking zero and empy cells into consideration...

    So You manage to fix it and now it is working properly?

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Return when a colomn turns and stays negative

    =IF(MAX(INDEX((LEN(J26:AO26)>0)*(J26:AO26>=0)*(COLUMN(J26:AO26)-COLUMN(J26)+1),0))>=COUNTA(J26:AO26),"No Outstanding",OFFSET(J$1,0,MAX(INDEX((LEN(J26:AO26)>0)*(J26:AO26>=0)*(COLUMN(J26:AO26)-COLUMN(J26)+1),0))))
    try this
    Attached Files Attached Files
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    04-10-2013
    Location
    Pila Poland
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Return when a colomn turns and stays negative

    Hi Both Miroslav and NFLsales ()

    I was able to get things working from your initial suggestions Miroslav - but after MFLsales edition - lookups are ignoring zero and empty values - Thank both of you soo much for taking your time to help out a fellow Excel fan.
    I see that I have much to learn to reach your levels still.
    Jan LP

  7. #7
    Valued Forum Contributor Miroslav R.'s Avatar
    Join Date
    05-16-2013
    Location
    NMnV, Slovakia
    MS-Off Ver
    Excel 2007
    Posts
    479

    Re: Return when a colomn turns and stays negative

    You are welcome!

+ 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: 4
    Last Post: 10-29-2012, 10:26 AM
  2. how to apply Formula at all colomn without the first colomn
    By ramiyousef in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-19-2012, 03:31 AM
  3. How I can analyse cells in colomn?
    By AleXis6 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-28-2011, 01:39 PM
  4. [SOLVED] colomn to row
    By Robert helou in forum Excel General
    Replies: 1
    Last Post: 08-10-2006, 10:00 AM
  5. [SOLVED] sum of colomn..
    By Zadig Galbaras in forum Excel General
    Replies: 2
    Last Post: 02-07-2005, 04:06 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