Fixed formula for running P/L

[CunningStunts]

Hi guys,

I'm using this formula =SUM($G$38:G38) to run a cumulative profit and loss, however when I play around with the data, it gets all mixed up and doesn't use the relative cells. How can I make sure it stays assigned to the same cell?

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[CunningStunts]

Thanks for the advice, I've done exactly that and attached my sample.

If you open the sample, the issue I'm having is with Column DI, as of now it's correct and works as a cumulative profit and loss from the results of Column DF. However, if you change of the date elsewhere, the formula falls apart, any idea how to fix this please? I would love to be able to filter the data around and the DI formula still follows the results from DF column

[Glenn Kennedy]

What changes have you made, that make it fall apart???

[CunningStunts]

Any changes at all make it fall apart for example if you filter the price from the DF column, even getting rid of one price it changes. Oh and I just realised in my explanation above, its the DH column that I want the DI column to represent as the cumulative profit and loss, not DF which I said above.

[Glenn Kennedy]

I do not see anything wrong. Maybe I'm being dense... it happens!! Step by step, take me through this...

[CunningStunts]

Not at all mate, most likely how I'm explaining it, I'll try be as clear as possible. Thanks for doing this btw

So, when you open the worksheet, if you highlight column DH, it totals 3.95
You can also see that the bottom figure in the DI column shows 3.95

I always need these two columns to match up but if you now go to column DF and filter it manually and untick 3.4 and 3.45 (this is just a random example)

The DH column now shows a total of 4.45 but (and this is where the issue is as DI should be the running total of the DH column) DI's bottom figure 3.95 so they now don't match up

Hope this is more clear. It doesn't matter what changes are made, the running total doesn't match up once any filtering is done

[Glenn Kennedy]

OK> SUBTOTAL always excludes filtered rows... so when you exclude a few values using filters, you only get the sum of the visible rows. If you want the total of the lot, use:

=SUM(DH$2:$DH$7324)

[CunningStunts]

Thank you mate, I'm still having a slight issue, I've input the formula but when I scroll it down to fill all the cells, it's not updating to the relevant cell. My DI column is showing 0.95 for every row instead of showing the cumulative total

[Glenn Kennedy]

One of us is misunderstanding the other. Which way round it is, I do not know. The formual I gave you was intended for row 1 ONLY. Not to be copied anywhere. I have amended your sheet.

DH1 is unchanged.

DJ1 is the formula I suggested.

I have filtered a few values OUT. Is what you see correct or incorrect? If not, what value shaould you see in which cell(s) and why?

Please be specific about the what and wheres!!

[CunningStunts]

Ok I'll try to be as clear as possible. I've downloaded your sheet and taken a look so from what I can see the something isn't working properly still because the DH column is totaling 3.50, whereas the DI column is now totaling 3.95

What I'm looking for is that both of those columns always match up no matter no I filter the sheet

Say I loaded my sheet fresh, is there anyway I could input the formula into column DI, drag it down all the cells to the bottom so it's always showing the running total from column DH AND if I filtered the sheet, it updates the totals in the DI column to still match up because right now only one problem seems to be solved, its either I can't drag the formula down because it's fixed or if I filter, it doesn't match up

I appreciate the help alot

[CunningStunts]

Bump if anyone can help me here please

[Glenn Kennedy]

The formula that you have in DH1 is

=SUBTOTAL(9,DH$2:DH7324)

if you re-read what I said at Post 8... you will NEVER get that formula to give you a total-total. SUBTOTAL always, always excludes filtered-out rows. That is why I suggested using =SUM(DH$2:DH7324) in DH1.

There is no need to copy/paste anything down the row. If you do that will mess up your PL calculations.

In case of continuing misunderstanding... See sheet. Amend it MANUALLY, to show exactly what you want to see, where you want to see it and re-post the sheet. I have cut it back to just 10 rows.

[CunningStunts]

I'm starting to think what I'm looking for can't be done.

Is there perhaps another type of formula or way of doing things to achieve what I want? I basically just want to be able to filter the sheet and the running total (DI) always matches up with DH

[Glenn Kennedy]

Is there some reason why you are unwilling to amend the sheet to show your expected results, calculated manually?

[CunningStunts]

No reason other than I don't know what to put, I'm a bit lost as to what to put on the sheet, I don't know how to explain or show it other than what I have said above

[Glenn Kennedy]

After 16 posts, it is clear that there is an ambiguity in your words, or a problem with my understanding of your words. That is why I have asked TWICE to let me SEE what you mean.


Over to others.

I'm out.

[CunningStunts]

This is the best I can do, if I filter the sheet which I've done in the example and took out the price of 4 from your sheet, why doesn't DI show 1.15

[Glenn Kennedy]

Finally, it a bit clearer. Row 3 is not filtered out, you have just made it so narrow that you cannot see it. Is that what you meant, or do you mean that it should have been filtered out?

[CunningStunts]

Oh right, so to me, I did filter it out because I chose the drop down menu for the price and unticked 4.

But you are right, how come it hasn't filtered it and instead just hidden it?

It should definitely have been filtered out but it hasn't

[Glenn Kennedy]

Ignore previous post... try this

[CunningStunts]

Perfect!!!!! That's exactly what I'm looking for.

Can you explain the formula just so I know how it works please?

Thanks for not giving up on me

[Glenn Kennedy]

Phew. that took a while. Once you start going in circles, it's hard to break free....

=SUBTOTAL(9,DH$3:DH3)

Red - the function number... 9 = SUM

The SUBTOTAL function will not count any rows that are filtered out.

Cyan. subtotal in D3 to d3

when copied down, the formula becomes $DH$3:DH4... so it subtotals from D3 to D4, etc, etc.



You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

[CunningStunts]

Works absolutely perfect, thanks again for the help mate

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It would also be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.