Automatic calculation based on a type

[ripdaman]

I have the data as per the attached file. What is desired is that the Balance Column gets calculated and updated automatically based on the Station type i.e. Col E should be updated automatically based on Col F. Further, if Taxes and Other charges are blank, the Balance should also be blank for that cell. I have entered the formulae manually in Column E which I want to automate.

Thanks

1.xlsx
	A	B	C	D	E	F
1	AMOUNT ADDED	AMOUNT PAID	TAXES	OTHER CHARGES	BALANCE	STATION
2
3		₹ 0.00				STATION A
4		₹ 0.00				STATION A
5		₹ 0.00				STATION A
6		₹ 0.00				STATION A
7		₹ 0.00				STATION A
8		₹ 0.00				STATION A
9	₹ 1,500	₹ 752.60	₹ 0	₹ 0.00	₹ 747.40	STATION B
10		₹ 0.00				STATION A
11	₹ 1,289	₹ 638.63	₹ 0	₹ 0.00	₹ 650.37	STATION C
12		₹ 0.00				STATION A
13	₹ 0	₹ 122.27	₹ 0	₹ 0.00	₹ 625.13	STATION B
14		₹ 0.00				STATION A
15		₹ 0.00				STATION A
16		₹ 0.00				STATION A
17		₹ 0.00				STATION A
18		₹ 0.00				STATION A
19		₹ 0.00				STATION A
20		₹ 0.00				STATION A
21		₹ 0.00				STATION A
22	₹ 1,180	₹ 487.34	₹ 0	₹ 693.66	-₹ 1.00	STATION C
23	₹ 697	₹ 335.00	₹ 0	₹ 0.00	₹ 362.00	STATION D
24	₹ 1,180	₹ 310.10	₹ 0	₹ 869.90	₹ 0.00	STATION C
25	₹ 0	₹ 269.51	₹ 0	₹ 0.00	₹ 355.62	STATION B
26		₹ 0.00				STATION A
27		₹ 0.00				STATION A
28	₹ 1,000	₹ 639.91	₹ 0	₹ 0.00	₹ 360.09	STATION E
29	₹ 1,000	₹ 620.44	₹ 0	₹ 0.00	₹ 379.56	STATION B
30		₹ 0.00				STATION A
31	₹ 400	₹ 400.00	₹ 0	₹ 0.00	₹ 0.00	STATION F
32	₹ 1,000	₹ 513.76	₹ 0	₹ 486.34	-₹ 0.10	STATION G
33	₹ 0	₹ 313.13	₹ 0	₹ 0.00	₹ 46.96	STATION E
34		₹ 0.00				STATION A
35		₹ 0.00				STATION A
36	₹ 1,183	₹ 577.36	₹ 0	₹ 0.00	₹ 605.64	STATION C
37	₹ 1,034	₹ 355.43	₹ 34	₹ 0.00	₹ 644.57	STATION C
38	₹ 0	₹ 303.08	₹ 0	₹ 0.00	₹ 341.49	STATION C
Sheet1

Cell Formulas
Range	Formula
E9,E36:E37,E31:E32,E28:E29,E22:E23,E11	E9	=A9-B9-C9-D9
E13	E13	=E9-B13
E24	E24	=A34-B34-C34-D34
E25	E25	=E13-B25
E33	E33	=E28-B33-C33
E38	E38	=E37-B38-C38

 

[Sanjeev1976]

You can try the below :

Book3
	A	B	C	D	E	F
1	AMOUNT ADDED	AMOUNT PAID	TAXES	OTHER CHARGES	BALANCE	STATION
2		-			-	STATION A
3		-			-	STATION A
4		-			-	STATION A
5		-			-	STATION A
6		-			-	STATION A
7		-			-	STATION A
8	1,500.00	752.60	-	-	747.40	STATION B
9		-			-	STATION A
10	1,289.00	638.63	-	-	650.37	STATION C
11		-			-	STATION A
12	-	122.27	-	-	625.13	STATION B
13		-			-	STATION A
14		-			-	STATION A
15		-			-	STATION A
16		-			-	STATION A
17		-			-	STATION A
18		-			-	STATION A
19		-			-	STATION A
20		-			-	STATION A
21	1,180.00	487.34	-	693.66	649.37	STATION C
22	697.00	335.00	-	-	362.00	STATION D
23	1,180.00	310.10	-	869.90	649.37	STATION C
24	-	269.51	-	-	355.62	STATION B
25		-			-	STATION A
26		-			-	STATION A
27	1,000.00	639.91	-	-	360.09	STATION E
28	1,000.00	620.44	-	-	735.18	STATION B
29		-			-	STATION A
30	400.00	400.00	-	-	-	STATION F
31	1,000.00	513.76	-	486.34	(0.10)	STATION G
32	-	313.13	-	-	46.96	STATION E
33		-			-	STATION A
34		-			-	STATION A
35	1,183.00	577.36	-	-	1,255.01	STATION C
36	1,034.00	355.43	34.00	-	1,899.58	STATION C
37	-	303.08	-	-	1,596.50	STATION C
Sheet1

Cell Formulas
Range	Formula
E2:E37	E2	=XLOOKUP(F2,$F$1:F1,$E$1:E1,0,0,-1)+A2-B2-C2-D2

 

[Peter_SSs]

It looks to me that some of your manual formulas are incorrect. For example your formula in E24 (Station C) refers to row 34 below (Station A).
Here is another option which shows 0 where relevant.

25 03 08.xlsm
	A	B	C	D	E	F
1	AMOUNT ADDED	AMOUNT PAID	TAXES	OTHER CHARGES	BALANCE	STATION
2
3		0				STATION A
4		0				STATION A
5		0				STATION A
6		0				STATION A
7		0				STATION A
8		0				STATION A
9	1500	752.6	0	0	747.40	STATION B
10		0				STATION A
11	1289	638.63	0	0	650.37	STATION C
12		0				STATION A
13	0	122.27	0	0	625.13	STATION B
14		0				STATION A
15		0				STATION A
16		0				STATION A
17		0				STATION A
18		0				STATION A
19		0				STATION A
20		0				STATION A
21		0				STATION A
22	1180	487.34	0	693.66	649.37	STATION C
23	697	335	0	0	362.00	STATION D
24	1180	310.1	0	869.9	649.37	STATION C
25	0	269.51	0	0	355.62	STATION B
26		0				STATION A
27		0				STATION A
28	1000	639.91	0	0	360.09	STATION E
29	1000	620.44	0	0	735.18	STATION B
30		0				STATION A
31	400	400	0	0	0.00	STATION F
32	1000	513.76	0	486.34	-0.10	STATION G
33	0	313.13	0	0	46.96	STATION E
34		0				STATION A
35		0				STATION A
36	1183	577.36	0	0	1,255.01	STATION C
37	1034	355.43	34	0	1,899.58	STATION C
38	0	303.08	0	0	1,596.50	STATION C
Balance

Cell Formulas
Range	Formula
E3:E38	E3	=IF(MAX(A3:D3)=0,"",LET(f,FILTER(A$3:D3,F$3:F3=F3),SUM(TAKE(f,,1),-DROP(f,,1))))

 

[ripdaman]

It looks to me that some of your manual formulas are incorrect. For example your formula in E24 (Station C) refers to row 34 below (Station A).
Here is another option which shows 0 where relevant.

25 03 08.xlsm
	A	B	C	D	E	F
1	AMOUNT ADDED	AMOUNT PAID	TAXES	OTHER CHARGES	BALANCE	STATION
2
3		0				STATION A
4		0				STATION A
5		0				STATION A
6		0				STATION A
7		0				STATION A
8		0				STATION A
9	1500	752.6	0	0	747.40	STATION B
10		0				STATION A
11	1289	638.63	0	0	650.37	STATION C
12		0				STATION A
13	0	122.27	0	0	625.13	STATION B
14		0				STATION A
15		0				STATION A
16		0				STATION A
17		0				STATION A
18		0				STATION A
19		0				STATION A
20		0				STATION A
21		0				STATION A
22	1180	487.34	0	693.66	649.37	STATION C
23	697	335	0	0	362.00	STATION D
24	1180	310.1	0	869.9	649.37	STATION C
25	0	269.51	0	0	355.62	STATION B
26		0				STATION A
27		0				STATION A
28	1000	639.91	0	0	360.09	STATION E
29	1000	620.44	0	0	735.18	STATION B
30		0				STATION A
31	400	400	0	0	0.00	STATION F
32	1000	513.76	0	486.34	-0.10	STATION G
33	0	313.13	0	0	46.96	STATION E
34		0				STATION A
35		0				STATION A
36	1183	577.36	0	0	1,255.01	STATION C
37	1034	355.43	34	0	1,899.58	STATION C
38	0	303.08	0	0	1,596.50	STATION C
Balance

Cell Formulas
Range	Formula
E3:E38	E3	=IF(MAX(A3:D3)=0,"",LET(f,FILTER(A$3:D3,F$3:F3=F3),SUM(TAKE(f,,1),-DROP(f,,1))))

Works perfectly. Thanks!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.