Calculate Total Expense With Condition

[kumara_faith]

Hi,

I have the following table:

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
3	Month	Expense Category	Amount		Today	5/12/2024		Month	Jan	Feb	Mar	Apr	May	June	July	August	September	October	November	December
4	Jan	Loans	5000					Average	45	60	59	43	11
5	Jan	Food	50					Total Actual	5225	5301	5293	5217	5053
6	Jan	Automobile	60					Total Estimated					6041
7	Jan	Medical	80
8	Jan	Food	20
9	Jan	Temple	15
10	Feb	Loans	5000
11	Feb	Food	150
12	Feb	Automobile	65
13	Feb	Medical	45
14	Feb	Food	23
15	Feb	Temple	18
16	Mar	Loans	5000
17	Mar	Food	110
18	Mar	Automobile	65
19	Mar	Medical	78
20	Mar	Food	15
21	Mar	Temple	25
22	Apr	Loans	5000
23	Apr	Food	125
24	Apr	Automobile	25
25	Apr	Medical	17
26	Apr	Food	35
27	Apr	Temple	15
28	May	Loans	5000
29	May	Food	10
30	May	Automobile	12
31	May	Medical	14
32	May	Food	12
33	May	Temple	5
Sheet1

The expense details is in the first table. In the second table, I am trying to calculate the average per month excluding the Loans expenses which is the outlier for every month. The total column includes the loans expense. I am trying to calculate the following:

1. The average expense for every month excluding the Loans in row J4:U4
2. In rowJ6:U6, I am trying to calculate the total expense for the current month based on the average expense for Jan-Apr . This will differ based on the current month. If the current date is June, then it will be Jan-May and so on.

The average expense per day for Jan-Apr excluding Loans is 52. The current date is 12 May 2024 with a balance of 19 days in May. Average expense of 52 X 19 days +5000(Loans) will result in 5988. Assuming total expense for May as of 12 May 2024 excluding Loans is 53, then total expense for May will be 6041

I tried the following but it did not work:

=IFERROR((AVERAGEIFS($D$4:$D$100000, $B$4:$B$100000, "<="&N3, $C$4:$C$100000, "<>"&"Loans") * (EOMONTH(G3,0)-G3+1)) + SUMIFS($D$4:$D$100000, $B$4:$B$100000, N3, $C$4:$C$100000, "Loans"), 0)

Please note that the actual range is from b4:b100000. This is a working file and the expenses will be entered everyday. The formula range will from B4:B100000 and the amount will be in the range d4:d10000. Both range will will have empty values as we enter the data every month from Jan to Dec 2024.



Is there a formula that could be used to accomplish this ? Appreciate all the help.

 

[Fluff]

How about

=AVERAGEIFS($D:$D,$B:$B,J$3,$C:$C,"<>Loans")

 

[Cubist]

Assuming this sheet is only 2024. Adapt the ranges.

Book1
	I	J	K	L	M	N	O
3	Month	Jan	Feb	Mar	Apr	May	June
4	Average	45.00	60.20	58.60	43.40	10.60
5	Total Actual	5,225.00	5,301.00	5,293.00	5,217.00	5,053.00
6	Total Estimated					5,984.20
Sheet3

Cell Formulas
Range	Formula
J4:N4	J4	=AVERAGEIFS($D$4:$D$33,$B$4:$B$33,J3,$C$4:$C$33,"<>Loans")
J5:N5	J5	=SUMIFS($D$4:$D$33,$B$4:$B$33,J3)
J6:N6	J6	=LET(loan,INDEX($D$4:$D$33,MATCH(J3&"Loans",$B$4:$B$33&"Loans",0)), d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3), avg,AVERAGEIFS($D$4:$D$33,$C$4:$C$33,"<>Loans",$B$4:$B$33,"<>"&J3), IF(d<0,"",loan+d*avg))

 

[kumara_faith]

Hi Cubist,

The formula for J6 does not seems to take into account the expenses incurred for May up to the current date which is 53 (excluding loans which the above formula has already included). Is there a way to include the expenses up to the current date for May to arrive at the correct total estimated expense for May ? Thanks.

 

[Cubist]

We can take advantage of the total in row 5.

=LET(loan,INDEX($D$4:$D$33,MATCH(J3&"Loans",$B$4:$B$33&"Loans",0)),
exp,J5-loan,
d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3),avg,AVERAGEIFS($D$4:$D$33,$C$4:$C$33,"<>Loans",$B$4:$B$33,"<>"&J3),
IF(d<0,"",loan+exp+d*avg))

 

[Cubist]

There are some redundant calculations in post#5. It can simplify to this.

=LET(
d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3),
avg,AVERAGEIFS($D$4:$D$33,$C$4:$C$33,"<>Loans",$B$4:$B$33,"<>"&J3),
IF(d<0,"",J5+d*avg))

 

[kumara_faith]

Hi Cubist,

Thank you and that worked. Really appreciate it.

When I drag the formula across to Jan to Apr, the formula shows blank which is correct because the months has passed. When I drag to June to Dec, by right the formula should also show blank because the months are in the future. But the formula seems to show estimated figures. Is there a way to modify this ?

 

[Cubist]

See below for the updated formula to not show for the other months. One thing I noticed one thing about your methodology of calculating the average. What you're calculating is the average monthly expense, not per day, so it's not quite correct. See how the result N6 compares to N7.

Book1
	I	J	K	L	M	N	O
3	Month	Jan	Feb	Mar	Apr	May	June
4	Average	45	60.2	58.6	43.4	10.6
5	Total Actual	5,225	5,301	5,293	5,217	5,053
6	Total Estimated					6,037
7	Total Estimated*					5,700
Sheet10

Cell Formulas
Range	Formula
J4:N4	J4	=AVERAGEIFS($D$4:$D$33,$B$4:$B$33,J3,$C$4:$C$33,"<>Loans")
J5:N5	J5	=SUMIFS($D$4:$D$33,$B$4:$B$33,J3)
J6:O6	J6	=LET( m,MONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1)), d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3), avg,AVERAGEIFS($D$4:$D$33,$C$4:$C$33,"<>Loans",$B$4:$B$33,"<>"&J3), IF(m<>MONTH($G$3),"",J5+d*avg))
J7:O7	J7	=LET( m,MONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1)), d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3), avg,SUMIFS($D$4:$D$33,$B$4:$B$33,"<>"&J3,$C$4:$C$33,"<>Loans")/30.44, IF(m<>MONTH($G$3),"",J5+d*avg))

 

[Cubist]

I didn't divide by the number of months above. The result should be closer to other months.

Book1
	I	J	K	L	M	N	O
3	Month	Jan	Feb	Mar	Apr	May	June
4	Average	45	60.2	58.6	43.4	10.6
5	Total Actual	5,225	5,301	5,293	5,217	5,053
6	Total Estimated					6,037
7	Total Estimated*					5,215
Sheet10

Cell Formulas
Range	Formula
J4:N4	J4	=AVERAGEIFS($D$4:$D$33,$B$4:$B$33,J3,$C$4:$C$33,"<>Loans")
J5:N5	J5	=SUMIFS($D$4:$D$33,$B$4:$B$33,J3)
J6:O6	J6	=LET( m,MONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1)), d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3), avg,AVERAGEIFS($D$4:$D$33,$C$4:$C$33,"<>Loans",$B$4:$B$33,"<>"&J3), IF(m<>MONTH($G$3),"",J5+d*avg))
J7:O7	J7	=LET( m,MONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1)), d,(EOMONTH(DATE(2024,MONTH(DATEVALUE("1-"&J3&"-2024")),1),0)-$G$3), avg,SUMIFS($D$4:$D$33,$B$4:$B$33,"<>"&J3,$C$4:$C$33,"<>Loans")/30.44/(m-1), IF(m<>MONTH($G$3),"",J5+d*avg))

 

[kumara_faith]

Hi Cubist,

Thank you for your patience and assistance. That worked. Really appreciate it and have a great day ahead.