Ratio calculation

[tigerzen]

Need a formula/function for Excel 2019 that calculates the ratio of money movements per each ending week. So for the first period we have a total of $12 coming in and $40 going out so the ratio for that week would be 12/40 which is 0.375. For the week ending 25th Aug we have $110 in and $60 going out so the ratio would be 110/60 which is 1.83

Book3
	A	B	C	D	E
2	Week ending	Client	Category	Money movement	Ratio calc
3	22-Sep-23	Sally	REF in	$12
4	22-Sep-23	Jack	REF out	-$40
5	8-Sep-23	Bert	REF out	-$90
6	8-Sep-23	Tom	REF out	-$30
7	25-Aug-23	Bert	REF in	$30
8	25-Aug-23	Mary	REF out	$60
9	25-Aug-23	Mary	REF in	$80
10	4-Aug-23	Tom	REF in	$83
11	4-Aug-23	Tom	REF in	$14
12	4-Aug-23	Sally	REF out	-$47
13	14-Jul-23	Sally	REF in	$1
14	14-Jul-23	Jack	REF out	-$63
15	30-Jun-23	Bert	REF in	$43
16	30-Jun-23	Tom	REF out	-$30
17	16-Jun-23	Sally	REF in	$31
18	16-Jun-23	Bert	REF in	$1
19	16-Jun-23	Jack	REF in	$47
20	26-May-23	Jack	REF in	$96
21	26-May-23	Sally	REF in	$85
22	26-May-23	Jack	REF out	-$95
23	26-May-23	Bert	REF in	$30
24	28-Apr-23	Bert	REF out	-$52
25	28-Apr-23	Jack	REF in	$93
26	14-Apr-23	Sally	REF out	-$68
27	14-Apr-23	Sally	REF in	$74
28	14-Apr-23	Sally	REF out	-$91
29	14-Apr-23	Bert	REF in	$95
30	17-Mar-23	Sally	REF out	-$83
Sheet1

 

[kevin9999]

Something like this maybe? You haven't said what you want done with weeks with only outs, or ins?

Book1
	A	B	C	D	E
1	Week ending	Client	Category	Money movement	Ratio calc
2	22-Sep-23	Sally	REF in	$12	0.300
3	22-Sep-23	Jack	REF out	-$40	0.300
4	8-Sep-23	Bert	REF out	-$90	0.000
5	8-Sep-23	Tom	REF out	-$30	0.000
6	25-Aug-23	Bert	REF in	$30	1.833
7	25-Aug-23	Mary	REF out	$60	1.833
8	25-Aug-23	Mary	REF in	$80	1.833
9	4-Aug-23	Tom	REF in	$83	2.064
10	4-Aug-23	Tom	REF in	$14	2.064
11	4-Aug-23	Sally	REF out	-$47	2.064
12	14-Jul-23	Sally	REF in	$1	0.016
13	14-Jul-23	Jack	REF out	-$63	0.016
14	30-Jun-23	Bert	REF in	$43	1.433
15	30-Jun-23	Tom	REF out	-$30	1.433
16	16-Jun-23	Sally	REF in	$31	0.000
17	16-Jun-23	Bert	REF in	$1	0.000
18	16-Jun-23	Jack	REF in	$47	0.000
19	26-May-23	Jack	REF in	$96	2.221
20	26-May-23	Sally	REF in	$85	2.221
21	26-May-23	Jack	REF out	-$95	2.221
22	26-May-23	Bert	REF in	$30	2.221
23	28-Apr-23	Bert	REF out	-$52	1.788
24	28-Apr-23	Jack	REF in	$93	1.788
25	14-Apr-23	Sally	REF out	-$68	1.063
26	14-Apr-23	Sally	REF in	$74	1.063
27	14-Apr-23	Sally	REF out	-$91	1.063
28	14-Apr-23	Bert	REF in	$95	1.063
29	17-Mar-23	Sally	REF out	-$83	0.000
Sheet1

Cell Formulas
Range	Formula
E2:E29	E2	=IFERROR(SUMIFS(D:D,A:A,A2,C:C,"*in*")/ABS(SUMIFS(D:D,A:A,A2,C:C,"*out*")),0)

 

[tigerzen]

Something like this maybe? You haven't said what you want done with weeks with only outs, or ins?

Book1
	A	B	C	D	E
1	Week ending	Client	Category	Money movement	Ratio calc
2	22-Sep-23	Sally	REF in	$12	0.300
3	22-Sep-23	Jack	REF out	-$40	0.300
4	8-Sep-23	Bert	REF out	-$90	0.000
5	8-Sep-23	Tom	REF out	-$30	0.000
6	25-Aug-23	Bert	REF in	$30	1.833
7	25-Aug-23	Mary	REF out	$60	1.833
8	25-Aug-23	Mary	REF in	$80	1.833
9	4-Aug-23	Tom	REF in	$83	2.064
10	4-Aug-23	Tom	REF in	$14	2.064
11	4-Aug-23	Sally	REF out	-$47	2.064
12	14-Jul-23	Sally	REF in	$1	0.016
13	14-Jul-23	Jack	REF out	-$63	0.016
14	30-Jun-23	Bert	REF in	$43	1.433
15	30-Jun-23	Tom	REF out	-$30	1.433
16	16-Jun-23	Sally	REF in	$31	0.000
17	16-Jun-23	Bert	REF in	$1	0.000
18	16-Jun-23	Jack	REF in	$47	0.000
19	26-May-23	Jack	REF in	$96	2.221
20	26-May-23	Sally	REF in	$85	2.221
21	26-May-23	Jack	REF out	-$95	2.221
22	26-May-23	Bert	REF in	$30	2.221
23	28-Apr-23	Bert	REF out	-$52	1.788
24	28-Apr-23	Jack	REF in	$93	1.788
25	14-Apr-23	Sally	REF out	-$68	1.063
26	14-Apr-23	Sally	REF in	$74	1.063
27	14-Apr-23	Sally	REF out	-$91	1.063
28	14-Apr-23	Bert	REF in	$95	1.063
29	17-Mar-23	Sally	REF out	-$83	0.000
Sheet1

Cell Formulas
Range	Formula
E2:E29	E2	=IFERROR(SUMIFS(D:D,A:A,A2,C:C,"*in*")/ABS(SUMIFS(D:D,A:A,A2,C:C,"*out*")),0)

 

[tigerzen]

Thanks Kevin, your formula works beautifully and yes you're right I omitted to mention that we need a summary by week so it should read 22 Sep 2023 0.3, 8 Sep 2023 0, 25 Aug 2023 1.83 etc, so in other words every unique week needs a ratio calculation. It can be done with a helper column, although I could just use filters or remove duplicates. It would be better though if we could come up with a dynamic formula to calculate this.

 

[kevin9999]

we need a summary by week so it should read 22 Sep 2023 0.3

Like this maybe?

Book1
	A	B	C	D	E
1	Week ending	Client	Category	Money movement	Ratio calc
2	22-Sep-23	Sally	REF in	12	22 Sep 2023 0.300
3	22-Sep-23	Jack	REF out	-40
4	8-Sep-23	Bert	REF out	-90	08 Sep 2023 0.000
5	8-Sep-23	Tom	REF out	-30
6	25-Aug-23	Bert	REF in	30	25 Aug 2023 1.833
7	25-Aug-23	Mary	REF out	60
8	25-Aug-23	Mary	REF in	80
9	4-Aug-23	Tom	REF in	83	04 Aug 2023 2.064
10	4-Aug-23	Tom	REF in	14
11	4-Aug-23	Sally	REF out	-47
12	14-Jul-23	Sally	REF in	1	14 Jul 2023 0.016
13	14-Jul-23	Jack	REF out	-63
14	30-Jun-23	Bert	REF in	43	30 Jun 2023 1.433
15	30-Jun-23	Tom	REF out	-30
16	16-Jun-23	Sally	REF in	31	16 Jun 2023 0.000
17	16-Jun-23	Bert	REF in	1
18	16-Jun-23	Jack	REF in	47
19	26-May-23	Jack	REF in	96	26 May 2023 2.221
20	26-May-23	Sally	REF in	85
21	26-May-23	Jack	REF out	-95
22	26-May-23	Bert	REF in	30
23	28-Apr-23	Bert	REF out	-52	28 Apr 2023 1.788
24	28-Apr-23	Jack	REF in	93
25	14-Apr-23	Sally	REF out	-68	14 Apr 2023 1.063
26	14-Apr-23	Sally	REF in	74
27	14-Apr-23	Sally	REF out	-91
28	14-Apr-23	Bert	REF in	95
29	17-Mar-23	Sally	REF out	-83	17 Mar 2023 0.000
Sheet1

Cell Formulas
Range	Formula
E2:E29	E2	=IF(A2<>A1,TEXT(A2,"dd mmm yyyy ")&TEXT(IFERROR(SUMIFS(D:D,A:A,A2,C:C,"*in*")/ABS(SUMIFS(D:D,A:A,A2,C:C,"*out*")),0),"0.000"),"")

 

[tigerzen]

Like this maybe?

Book1
	A	B	C	D	E
1	Week ending	Client	Category	Money movement	Ratio calc
2	22-Sep-23	Sally	REF in	12	22 Sep 2023 0.300
3	22-Sep-23	Jack	REF out	-40
4	8-Sep-23	Bert	REF out	-90	08 Sep 2023 0.000
5	8-Sep-23	Tom	REF out	-30
6	25-Aug-23	Bert	REF in	30	25 Aug 2023 1.833
7	25-Aug-23	Mary	REF out	60
8	25-Aug-23	Mary	REF in	80
9	4-Aug-23	Tom	REF in	83	04 Aug 2023 2.064
10	4-Aug-23	Tom	REF in	14
11	4-Aug-23	Sally	REF out	-47
12	14-Jul-23	Sally	REF in	1	14 Jul 2023 0.016
13	14-Jul-23	Jack	REF out	-63
14	30-Jun-23	Bert	REF in	43	30 Jun 2023 1.433
15	30-Jun-23	Tom	REF out	-30
16	16-Jun-23	Sally	REF in	31	16 Jun 2023 0.000
17	16-Jun-23	Bert	REF in	1
18	16-Jun-23	Jack	REF in	47
19	26-May-23	Jack	REF in	96	26 May 2023 2.221
20	26-May-23	Sally	REF in	85
21	26-May-23	Jack	REF out	-95
22	26-May-23	Bert	REF in	30
23	28-Apr-23	Bert	REF out	-52	28 Apr 2023 1.788
24	28-Apr-23	Jack	REF in	93
25	14-Apr-23	Sally	REF out	-68	14 Apr 2023 1.063
26	14-Apr-23	Sally	REF in	74
27	14-Apr-23	Sally	REF out	-91
28	14-Apr-23	Bert	REF in	95
29	17-Mar-23	Sally	REF out	-83	17 Mar 2023 0.000
Sheet1

Cell Formulas
Range	Formula
E2:E29	E2	=IF(A2<>A1,TEXT(A2,"dd mmm yyyy ")&TEXT(IFERROR(SUMIFS(D:D,A:A,A2,C:C,"*in*")/ABS(SUMIFS(D:D,A:A,A2,C:C,"*out*")),0),"0.000"),"")

Thanks Kevin, that will work great.

 

[kevin9999]

Thanks Kevin, that will work great.

Happy to help and thanks for the feedback