Dynamic formula for subtracting from two different cells.

[Ali3ta1r]

Hi,

I am not sure if this is a simple question but seeking some advice on the below.

Is there a simple subtraction formula I can use between 2 cells that would work/update accordingly on visible data only?

At the moment I am using =A1-A2, but when I filter this it still accounts for the hidden data,

Kind Regards,
Alistair

 

[Ali3ta1r]

I see Red alerts on the column M, N, O & P. what did They tell?(Topleft corner of each cell)
I see N2=L2*(I2-1) what is I .

I is the value that Column M is calculated from. A lengthy explanation of what it represents exactly, but it's a number.

It doesn't always apply, hence why sometimes Column M & N are blank.

It's also why I would like to filter by account and the data to update accordingly.

 

[maabadi]

What is Your column format? (number Format)
and click on one of red alert and show it.

 

[Ali3ta1r]

What is Your column format? (number Format)
and click on one of red alert and show it.

So it's currency format, £ and 2 decimals.

The red boxes have disappeared so I can't check.

 

[Ali3ta1r]

Book1
	K	L	M	N	O
1	Stake	New Bank	Returned	R.Stake	£0.00
2	£1.00	-£1.00	£20.00	£1.00	£21.00
3	£1.00	-£1.00	£10.49	£1.00	£11.49
4	£1.00	-£1.00			£0.00
5	£1.00	-£1.00			£0.00
6	£1.00	-£1.00			£0.00
7	£1.00	-£1.00			£0.00
8	£1.00	-£1.00			£0.00
9	£1.00	-£1.00			£0.00
ALL

Cell Formulas
Range	Formula
M2:M3	M2	=K2*(I2-1)
N2:N3	N2	=K2
L2:L9	L2	=SUBTOTAL(109,O1)-SUBTOTAL(109,K2)
O2:O9	O2	=SUBTOTAL(109,L2)+SUBTOTAL(109,M2)+SUBTOTAL(109,N2)

Why is Cell M3 showing -£1.00 when I want it to show £20 (calculation of Cell O2- Cell K2)?

 

[Ali3ta1r]

Book1
	K	L	M	N	O
1	Stake	New Bank	Returned	R.Stake	£0.00
2	£1.00	-£1.00	£20.00	£1.00	£21.00
3	£1.00	-£1.00	£10.49	£1.00	£11.49
4	£1.00	-£1.00			£0.00
5	£1.00	-£1.00			£0.00
6	£1.00	-£1.00			£0.00
7	£1.00	-£1.00			£0.00
8	£1.00	-£1.00			£0.00
9	£1.00	-£1.00			£0.00
ALL

Cell Formulas
Range	Formula
M2:M3	M2	=K2*(I2-1)
N2:N3	N2	=K2
L2:L9	L2	=SUBTOTAL(109,O1)-SUBTOTAL(109,K2)
O2:O9	O2	=SUBTOTAL(109,L2)+SUBTOTAL(109,M2)+SUBTOTAL(109,N2)

Why is Cell M3 showing -£1.00 when I want it to show £20 (calculation of Cell O2- Cell K2)?

Sorry, I mean Cell L3 (not M3)

 

[maabadi]

For Table at Post #8 , Use this formula for M2:

=IF(OR(COUNTA($K$1:K1)=SUBTOTAL(103,$K$1:K1),SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2)))=0),P1-L2,INDIRECT("P"&SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2))))-L2)

And for P2:

=SUBTOTAL(109,N2,O2,M2)

 

[Ali3ta1r]

For Table at Post #8 , Use this formula for M2:

=IF(OR(COUNTA($K$1:K1)=SUBTOTAL(103,$K$1:K1),SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2)))=0),P1-L2,INDIRECT("P"&SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2))))-L2)

And for P2:

=SUBTOTAL(109,N2,O2,M2)

Alistair.xlsx
	L	M	N	O	P
1	Stake	New Bank	Returned	R.Stake	£0.00
2	£1.00	-£1.00	£20.00	£1.00	£20.00
3	£1.00	£19.00	£10.49	£1.00	£30.49
4	£1.00	£29.49			£29.49
5	£1.00	£28.49			£28.49
6	£1.00	£27.49	£7.03	£1.00	£35.52
7	£1.00	£34.52			£34.52
8	£1.00	£33.52			£33.52
9	£1.00	£32.52			£32.52
ALL

Cell Formulas
Range	Formula
N2:N3,N6	N2	=L2*(I2-1)
O2:O3,O6	O2	=L2
M2:M9	M2	=IF(OR(COUNTA($K$1:K1)=SUBTOTAL(103,$K$1:K1),SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2)))=0),P1-L2,INDIRECT("P"&SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2))))-L2)
P2:P9	P2	=M2+N2+O2

So that has done something different, though when I filter the data according to Accounts it still uses values form the hidden cells.

So each Accounts' profit/loss still can't be calculated separately by filtering the data.

 

[maabadi]

OK. Then Use this (for M2 and drag it):

=IFERROR(INDIRECT("P"&SUMPRODUCT(MAX(ROW($K$1:K1)*(K$1:K1=K2))))-L2,INDIRECT("P"&SUBTOTAL(103,$K$1:K1))-L2)

 

[Ali3ta1r]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Time	Course	Horse	Bet	System	Result	Odds		Stake	New Bank	Winnings	Returned Stake	£0.00
2	18/09/2020	16.55	Ayr	Blonde Warrior	Win	A		12.60		£1.00	-£1.00			-£1.00
3	18/09/2020	17.20	Newbury	Always Fearless	Win	B		13.30		£1.00	-£2.00			-£2.00
4	18/09/2020	20.15	Kempton	Takeonefortheteam	Win	C		6.25		£1.00	-£3.00			-£3.00
5	18/09/2020	20.30	Dundalk	Sky Seven	Win	A		9.48		£1.00	-£4.00			-£4.00
6	19/09/2020	15.10	Gowran Park	Mr Wendell	Win	C	1st	5.28		£1.00	-£5.00	£4.28	£1.00	£0.28
7	20/09/2020	14.05	Hamilton	Shesadabber	Win	C	1st	7.40		£1.00	-£0.72	£6.40	£1.00	£6.68
8	20/09/2020	15.30	Chelmsford City	Global Art	Win	C	1st	13.00		£1.00	£5.68	£12.00	£1.00	£18.68
9	21/09/2020	15.50	Fairyhouse	Line Judge	Win	A		11.00		£1.00	£17.68			£17.68
10	21/09/2020	17.05	Hamilton	John Jasper	Win	A		8.20		£1.00	£16.68			£16.68
11	22/09/2020	14.30	Lingfield	Hiroshi	Win	A		4.27		£1.00	£15.68			£15.68
12	22/09/2020	16.15	Lingfield	Cappananty Con	Win	A		5.97		£1.00	£14.68			£14.68
13	22/09/2020	16.55	Beverley	First Response	Win	A		5.34		£1.00	£13.68			£13.68
14	23/09/2020	19.00	Kempton	Never Alone	Win	A		5.70		£1.00	£12.68			£12.68
15	24/09/2020	13.00	Pontefract	Arceus	Win	C		6.75		£1.00	£11.68			£11.68
ALL

Cell Formulas
Range	Formula
L6:L8	L6	=J6*(H6-1)
M6:M8	M6	=J6
K2:K15	K2	=N1-J2
N2:N15	N2	=K2+L2+M2

Okay, still not working and I can't seem to get the red ! to return to check them.

However, it might help if I give some further data and information on the purpose of what I am seeking to do. So I bet on the horses using various systems (see Column F) and I track my results of all the systems on one single Excel sheet.

The starting bank is £0.00 (Cell N1) and I proof my systems to a level stakes bet of £1.00 (Column J).
After each bet, I show my bank minus the stake/bet; which is always £1.00 (Column K).
If the horse wins, my winnings (Column L) are calculated by the stake/bed multiplied by the odds -1 (Column H)
My bank after each bet (Column N) is calculated by the new bank minus stake (Column K) + any returned winnings (Column L) + the returned stake (Column M).

As you can see in the above example, the profile/loss is showing all my systems compiled (Column F). What I would like to do is filter my Systems (Column F) and for the financial calculations (Columns K, L, M and N) to update accordinly. So I can quickly check the profitability of each system individually, as well as all compiled together.

Is this possible?

 

[maabadi]

That should be working, but you change your data at columns. this is modified version based your new table. Please report problems. Input at K2 and drag it down:

=IFERROR(INDIRECT("N"&SUMPRODUCT(MAX(ROW($F$1:F1)*(F$1:F1=F2))))-J2,INDIRECT("N"&SUBTOTAL(103,$F$1:F1))-J2)