+ Reply to Thread
Results 1 to 9 of 9

Correction Needed for Calculating Running Quantity

  1. #1
    Registered User
    Join Date
    06-10-2021
    Location
    Florida, US
    MS-Off Ver
    2016
    Posts
    33

    Correction Needed for Calculating Running Quantity

    Hello Everyone,

    Thanks in advance for taking the time to review my problem. Please see attached. This spreadsheet is a culmination of the hard work from so many great contributers from this community.

    Column J is for calculating a running quantity with each purchase and sale. Starting in J8, here is the current formula:
    =IF(AND(B7<>"Sale",ROUND(SUM(F$7:F8),0)>=0),J7+F8,F8)

    This works really good until we get down to J25 (highlighted in red) and we see something that doesn't make sense. If calculated properly, it should equal 40.69221224 (J24 + F25)

    With the current formula, instead of doing J24+F25, it simply drags over F25.

    How can this formula be corrected to do this right while still keeping all the correctly calculated values above it? I've tried a few things but can't seem to figure it out.

    Feel free to un-highlight the black columns if you need to see the rest of the data. I blacked them out as they aren't needed for this column and to help bring focus on where the problem comes in (red highlights).
    Attached Files Attached Files

  2. #2
    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,404

    Re: Correction Needed for Calculating Running Quantity

    It's doing what you told it to.

    =IF(AND(B24<>"Sale",ROUND(SUM(F$7:F25),0)>=0),J24+F25,F25)

    =IF(AND(B24<>"Sale", = FALSE

    ROUND(SUM(F$7:F25),0)>=0), = TRUE

    FALSE + TRUE = FALSE

    IF TRUE: J24+F25,

    IF FALSE: F25)

    Why are you expecting it to evaluate to TRUE?
    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.

  3. #3
    Registered User
    Join Date
    06-10-2021
    Location
    Florida, US
    MS-Off Ver
    2016
    Posts
    33

    Re: Correction Needed for Calculating Running Quantity

    I am aware it is doing what its being told. The request here is to improve the formula to calculate the values properly. It needs to be re-written to tell the proper story.

  4. #4
    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,404

    Re: Correction Needed for Calculating Running Quantity

    That's just a TOUCH sarcastic/patronising, don't you think? I am giving of my free time to help you, on a Sunday, for FREE!!!

    You have said what you want it to return, but you have not said WHY, so there's no hope of 'fixing' your formula from my point of view because you haven't told me what I need to know.

    So, tell us, please, what the 'proper story' is. I/we don't inherently know why you want that value instead of what the formula is currently producing. It looks like it needs another condition, but I cannot guess what that condition should be.

    The nature of your next response will determine whether or not I continue to offer help.
    Last edited by AliGW; 07-17-2021 at 09:29 AM. Reason: Typo

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Correction Needed for Calculating Running Quantity

    I have not understood the logic. On guess try this .
    In J7

    =IF(OR(B6<>"Sale",ROUND(SUM(F$7:F7),0)>=0),SUM(J6,F7),F7)
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  6. #6
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Correction Needed for Calculating Running Quantity

    The formula for J7 (not J8) should be a simple: =SUM(F$7:F7).
    Copy down.

  7. #7
    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,404

    Re: Correction Needed for Calculating Running Quantity

    That looks like a possibility. Hopefully the OP will confirm one way or the other, but if not, we will need to know the logic.

  8. #8
    Registered User
    Join Date
    06-10-2021
    Location
    Florida, US
    MS-Off Ver
    2016
    Posts
    33

    Re: Correction Needed for Calculating Running Quantity

    Thank you everyone for taking the time to help with this! kvsrinivasamurthy nailed it, it works perfect line-by-line to correctly add and subtract quantity on a line-by-line basis for proper portfolio tracking.

    I will mark this one as solved. I really appreciate everyone's help with this!

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Correction Needed for Calculating Running Quantity

    Thanks for feedback and Rep.

+ 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. Correction needed for Outbound Quantity
    By XanaduCSIV in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 08-29-2021, 02:37 AM
  2. [SOLVED] Running total of Quantity on Hand Vs Sales Vs Quantity to Produce
    By jespo1351 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2015, 07:34 AM
  3. Correction needed for transpose macro
    By genetist in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-08-2014, 04:03 AM
  4. If Function needed some correction in what I am doing wrong
    By chandannasta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-05-2014, 03:56 AM
  5. [SOLVED] Roundup correction needed
    By mikerules in forum Excel General
    Replies: 3
    Last Post: 03-27-2014, 10:12 PM
  6. [SOLVED] Nested For Loop - Correction Needed
    By joogibabu in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 04-25-2013, 11:14 PM
  7. Correction in Macro needed
    By maximpinto in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2009, 07:23 AM

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