# Thread: Need a running-total formula

1. ## 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

2. I suspect a worksheet sample would make it clearer. Can you post one?

cheers, Paul

3. 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. 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

5. What will you do if Item 1 and item 2 are SOLD?

6. Thanks Fred

Thanks again

Wassim

7. Originally Posted by kweaver
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

8. 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. 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.

10. Revised here.

11. 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. Hi Maud

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

zeddy

13. Stop competing you two!

cheers, Paul

14. Lol, that would be a losing battle for me!

15. Pretty close contest methinks.

cheers, Paul

Page 1 of 2 12 Last