Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Need a running-total formula

    Hello All,

    I am working on a template and I need your help. I am not sure if you could call it a "running total" but it does have some of the elements in common.

    OK let me explain.

    I have a list of items, say Item 1 through Item 600.

    These items are bought and sold.

    What I need is the following:
    When Item 17 is sold, Item 18 becomes Item 17, Item 19 becomes Item 18, Item 20 becomes Item 19 and so on and so forth.

    Now comes the glitch that I can't solve and need help with:

    When Item 17 is sold, Item 18 becomes Item 17, Item 19 becomes Item 18, Item 20 becomes Item 19 and so on and so forth.
    But when Item 111 is sold, Item 112 becomes Item 111, Item 113 becomes Item 112, Item 114 becomes Item 113 and so on and so forth.

    Because Item 17 was sold, Item 111 is now Item 110 and that needs to be identified as such.

    In other words when an Item has a lower numbered item sold then that Item number gets reduced by how many ever lower numbered items sold.

    Thanks for any insight and let me know if my explanation is understandable, or if I need to clarify things better.

    Cordially

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    I suspect a worksheet sample would make it clearer. Can you post one?

    cheers, Paul

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    let's say that col B has some indication that the item has been sold - like the word "sold".

    let's also say that row 1 is a header row.

    let's use col C for the new item number.

    Then put C1=0.

    In C2, put
    =if(B2<>"sold", C2=C1+1,C1)
    and fill down.

    this will change values in col C only when a row has an item not sold in col B. If the item was sold, then col C has the value of the previous row in col C.

    If you don't like having a number in col C for sold items, even tho it is not a "new" number, the same can be accomplished using the max formula.

    In this case, again with a 0 in C1
    =if(c2<>"sold", max($C$1,C1)+1,"----")
    and fill down

    or whatever you want in place of the dashes.

    Fred

  4. #4
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thanks Paul

    Here is a visual representation:

    Item Number ... Sold/Unsold ... New Item Number
    1...................U...................1
    2...................U...................2
    3...................S...................0
    4...................U...................3
    5...................U...................4
    6...................S...................0
    7...................U...................5
    8...................U...................6
    9...................U...................7
    10...................S...................0
    11...................U...................8
    12...................U...................9
    13...................U...................10
    14...................U...................11
    15...................U...................12
    16...................U...................13
    17...................U...................14
    18...................U...................15
    19...................U...................16
    20...................U...................17

    So as you can see its like musical chair, or a collapsible list. If an item is sold, the list of items such that Item # > Sold Item Number moves into its place. If you sell item 1 then all items lose 1 from their item number, and if you sell items 5 and 23 then Items with Items > 23 become Item Number -2, and then the items 6 through 22 become Item number -1.

    I hope this is a bit clearer, I may not be able to post an example I have to ask the client and see what they would say.

    Thanks a Million.

    Wassim
    Last edited by Wassim; 2015-02-02 at 12:01.
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    What will you do if Item 1 and item 2 are SOLD?

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thanks Fred

    You gave me some starting points, please see my additional reply to Paul.

    Thanks again

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post
    Quote Originally Posted by kweaver View Post
    What will you do if Item 1 and item 2 are SOLD?
    Hi kweaver

    Then all Items will have their numbers -2 so Item #3 becomes Item #1, Item #4 becomes Item #2 and Item #200 becomes Item #198.

    The problem I am having is to write the formula that would test the whole range and decide how many items have been sold and adjust the numbers accordingly. I can write a formula that can look at each cell but I need to look at the whole range.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    The "max" formula posted by Fburg would seem to be what you are looking for, except that you need to specify the MAX as a range.
    =if(c2<>"sold", max($C$1:C1)+1,"----")

    cheers, Paul

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Assuming row 1 is a header row, I put these two formulas in C2 and C3, respectively, then filled down C3

    =IF("U"=B2,A2,0)
    =IF("U"=B3,1+MAX($C$2:C2),IF(C2=COUNTA($B$2:B3)-COUNTIF($B$2:B3,"S"),0,2))


    RATS. Doesn't work if 2nd and 3rd are both SOLD. Back to the drawing board.
    Attached Files Attached Files
    Last edited by kweaver; 2015-02-02 at 14:10. Reason: found an error.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Revised here.
    Attached Files Attached Files

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    You may also consider this formula in cell C2 then copy down:
    =IF(B2="S",0,A2-COUNTIF($B$2:B2,"S"))

    Wassim.png

    HTH,
    Maud

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Maud

    ..or
    =IF(B2="U",COUNTIF(B$1:B2,"U"),0)
    ..which is marginally shorter

    zeddy

  13. #13
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    Stop competing you two!

    cheers, Paul

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Lol, that would be a losing battle for me!

  15. #15
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,162
    Thanks
    47
    Thanked 976 Times in 906 Posts
    Pretty close contest methinks.

    cheers, Paul

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •