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

 

[tusharm]

You could consider SUBTOTAL but it only supports SUM (among other functions, just no subtract capability). So, one workaround would be to enter in B2 the formula =-A2 and then use SUBTOTAL with A1 and B2. For more on SUBTOTAL check Google or Excel help.

 

[maabadi]

I think You can Use Subtotal function With Function Number 109, Example:
=A1-A2 converted to =SUBTOTAL(109,A1)-SUBTOTAL(109,A2)

 

[Ali3ta1r]

I think You can Use Subtotal function With Function Number 109, Example:
=A1-A2 converted to =SUBTOTAL(109,A1)-SUBTOTAL(109,A2)

I have just tried this and it hasn't worked as hoped.

I have attached a screenshot that I hope helps show what I am trying to achieve.

Essentially what I am trying to do is this:

1. When I filter "Column K" by Account, I would like "Column M" and "Column P" to update using only the filtered data.
2. To clarify, both "Column M" and "Column P" are dynamic values.
3. "Column M" is always calculated from the above line's corresponding value in "Column P" SUBTRACTING "Column L" (the Stake which is always £1.00)
4. "Column P" is calculated from the SUM of the same line's corresponding values in "Colum M, N & O".

I hope you can see from the attached photo that when I use the formula above, I always get -£1.00 in "Column M" (New Bank). Which is not correct, it should be the value of the above lines corresponding "Column P" value minus "Column L" (Stake). So in the photo attatched, Cell M3 should be P2-L3 (which should equal £20.00).

I hope that is clear? Any advice on how to achieve this?

 

[maabadi]

Please Use XL2BB to upload 4-5 row example of your files to we know what is your formula and if possible correct it.
You see XL2BB at above of new message section when you want write.
In that Situation M3 = N2 . Is correct this?
When all data in Column L is equal, Why M3=P2-L3 , why not M3= P2-L2
Are your format number is correct? M3 equal to:
=SUBTOTAL(109,P2)-SUBTOTAL(109,L3)

 

[Ali3ta1r]

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

Cell Formulas
Range	Formula
N2:N3,N6	N2	=L2*(I2-1)
O2:O3,O6	O2	=L2
M2:M11	M2	=P1-L2
P2:P11	P2	=SUM(M2:O2)

 

[Ali3ta1r]

I hope this helps, this is what I was using before and wasn't working as I require.

To clarify, I would like to be able to filter the data on "Column K" (Accounts) so the values in "Columns M, N O & P" update accordingly.

Difficult to explain, but any Q's please let me know and I'll do my best.

 

[Ali3ta1r]

Alistair.xlsx
	K	L	M	N	O	P
1	Account	Stake	New Bank	Returned	R.Stake	£0.00
2	A	£1.00	-£1.00	£20.00	£1.00	£21.00
3	B	£1.00	-£1.00	£10.49	£1.00	£11.49
4	C	£1.00	-£1.00			£0.00
5	C	£1.00	-£1.00			£0.00
6	C	£1.00	-£1.00	£7.03	£1.00	£8.03
7	C	£1.00	-£1.00			£0.00
8	A	£1.00	-£1.00			£0.00
9	C	£1.00	-£1.00			£0.00
10	B	£1.00	-£1.00			£0.00
11	B	£1.00	-£1.00			£0.00
ALL

Cell Formulas
Range	Formula
N2:N3,N6	N2	=L2*(I2-1)
O2:O3,O6	O2	=L2
M2:M11	M2	=SUBTOTAL(109,P1)-SUBTOTAL(109,L2)
P2:P11	P2	=SUBTOTAL(109,M2)+SUBTOTAL(109,N2)+SUBTOTAL(109,O2)

 

[Ali3ta1r]

And here's the 2nd one when I try to use the Subtotal formula.

Column M values are all wrong, which then makes Column P wrong.

i.e. M4 should be P3-L3 (equalling £10.49) and then P4 should be the sum of M3, N3 & O3 (in this instance still £10.49).

Then as noted above, I would like to be able to filter by Column K (Accounts) so that all the cells still calculate the same but only using the visible data.

 

[maabadi]

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 .