Conditional calculation of added columns - seeking a cleaver solution.

[tbobolz]

Hi, thanks for looking and offering a suggestion. I can't figure out a clean way to achieve this need.

Below row 6 displays fake revenue by month
Row 11 displays the calculate management fee of each matching month.
Management fee is the months revenue on row 6 time the percentage in cell B6.
Once the revenue reach 35 million in February, (running total adding November to January), then the management fee drops to the rate in cell C6
I'm seeking a formula of another way to populate cells C11 through N11. One that adds each month as each month passes and assigns a management fee to each cell of the month on row 11 depending on the current running total. Additionally, in the case below, in February the running total will hit 35 million, so part of that revenue must be calculated at 3.75% and the amount over 35 million would be calculated at 2%

So, cells C11 through E11 will calculate the correlated revenue at 3.75%. Cell F11 will calculate the revenue at 3.75 and 2%. The remaining months will calculate at 2%.

Yet each cell needs to evaluate the running total to insure the proper rate is used.

Thanks for any advice you might have.

 

[dreid1011]

Can you provide the expected results for Feb-October?

 

[tbobolz]

Can you provide the expected results for Feb-October?

Thanks for looking, I hope this helps

Terry

 

[dreid1011]

Edit: I see I need to adjust the formula.

Do these values look accurate?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Gain (Loss) of Subsidiary
2	FY 2025 Budgeted
3
4	Source
5	Entity	% of Net Revenue	% of Net Revenue	Max Net Revenue	November	December	January	February	March	April	May	June	July	August	September	October	Total
6	Entity 1	3.75%	2.00%	$35,000,000	$8,775,303	$8,846,167	$9,375,040	$8,723,556	$8,970,181	$8,185,842	$9,013,972	$8,633,654	$8,223,222	$8,088,077	$8,080,402	$8,210,384	$103,125,800
7
8
9		Management Fee
10		Account	November	December	January	February	March	April	May	June	July	August	September	October	Total
11		Entity 1	$329,074	$660,805	$1,012,369	$1,326,901	$1,506,305	$1,670,022	$1,850,301	$2,022,974	$2,187,439	$2,349,200	$2,510,808	$2,675,016	$20,101,215
Sheet2

Cell Formulas
Range	Formula
Q6,O11	Q6	=SUM(E6:P6)
C11:N11	C11	=SUM(MIN(SUM($E$6:E6),$D$6)*$B$6,MAX(0,SUM($E$6:E6)-$D$6)*$C$6)

Also, is there a need to match Account with Entity to pull the source info into management fee section?

 

[dreid1011]

Okay, try this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Gain (Loss) of Subsidiary
2	FY 2025 Budgeted
3
4	Source
5	Entity	% of Net Revenue	% of Net Revenue	Max Net Revenue	November	December	January	February	March	April	May	June	July	August	September	October	Total
6	Entity 1	3.75%	2.00%	$35,000,000	$8,775,303	$8,846,167	$9,375,040	$8,723,556	$8,970,181	$8,185,842	$9,013,972	$8,633,654	$8,223,222	$8,088,077	$8,080,402	$8,210,384	$103,125,800
7
8
9		Management Fee
10		Account	November	December	January	February	March	April	May	June	July	August	September	October	Total
11		Entity 1	$329,074	$331,731	$351,564	$314,532	$179,404	$163,717	$180,279	$172,673	$164,464	$161,762	$161,608	$164,208	$2,675,016
Sheet2

Cell Formulas
Range	Formula
Q6,O11	Q6	=SUM(E6:P6)
C11:N11	C11	=LET(s,SUM($E$6:E6),m,$D$6,b,$B$6,c,$C$6,IF(s<=m,E6*b,IF((s-m-E6)>=0,E6*c,SUM((s-m)*c,((E6-(s-m))*b)))))

And answer:

Also, is there a need to match Account with Entity to pull the source info into management fee section?

 

[dreid1011]

Also, which version of Excel are you using? You should update your profile to help get the most accurate solutions.

 

[tbobolz]

Edit: I see I need to adjust the formula.

Do these values look accurate?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Gain (Loss) of Subsidiary
2	FY 2025 Budgeted
3
4	Source
5	Entity	% of Net Revenue	% of Net Revenue	Max Net Revenue	November	December	January	February	March	April	May	June	July	August	September	October	Total
6	Entity 1	3.75%	2.00%	$35,000,000	$8,775,303	$8,846,167	$9,375,040	$8,723,556	$8,970,181	$8,185,842	$9,013,972	$8,633,654	$8,223,222	$8,088,077	$8,080,402	$8,210,384	$103,125,800
7
8
9		Management Fee
10		Account	November	December	January	February	March	April	May	June	July	August	September	October	Total
11		Entity 1	$329,074	$660,805	$1,012,369	$1,326,901	$1,506,305	$1,670,022	$1,850,301	$2,022,974	$2,187,439	$2,349,200	$2,510,808	$2,675,016	$20,101,215
Sheet2

Cell Formulas
Range	Formula
Q6,O11	Q6	=SUM(E6:P6)
C11:N11	C11	=SUM(MIN(SUM($E$6:E6),$D$6)*$B$6,MAX(0,SUM($E$6:E6)-$D$6)*$C$6)

Also, is there a need to match Account with Entity to pull the source info into management fee section?

Edit: I see I need to adjust the formula.

Do these values look accurate?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Gain (Loss) of Subsidiary
2	FY 2025 Budgeted
3
4	Source
5	Entity	% of Net Revenue	% of Net Revenue	Max Net Revenue	November	December	January	February	March	April	May	June	July	August	September	October	Total
6	Entity 1	3.75%	2.00%	$35,000,000	$8,775,303	$8,846,167	$9,375,040	$8,723,556	$8,970,181	$8,185,842	$9,013,972	$8,633,654	$8,223,222	$8,088,077	$8,080,402	$8,210,384	$103,125,800
7
8
9		Management Fee
10		Account	November	December	January	February	March	April	May	June	July	August	September	October	Total
11		Entity 1	$329,074	$660,805	$1,012,369	$1,326,901	$1,506,305	$1,670,022	$1,850,301	$2,022,974	$2,187,439	$2,349,200	$2,510,808	$2,675,016	$20,101,215
Sheet2

Cell Formulas
Range	Formula
Q6,O11	Q6	=SUM(E6:P6)
C11:N11	C11	=SUM(MIN(SUM($E$6:E6),$D$6)*$B$6,MAX(0,SUM($E$6:E6)-$D$6)*$C$6)

Also, is there a need to match Account with Entity to pull the source info into management fee section?

Thanks for the tips,

Playing around with your genius formula it is doing almost what we want. In November it is right on the mark, in December, I'd like it to only calculate the December total and not in in the prior months.

Also, I'll update my profile, i have not been on here in years and did not think of that when I reactivated the account. As for your second question, I do not think there's a need to match Entity and Account, although we will have multiple entity that I'd use this solution for in the final version.

Again, really appreciate you taking the time to help me.

Terry

 

[dreid1011]

Thanks for the tips,

Playing around with your genius formula it is doing almost what we want. In November it is right on the mark, in December, I'd like it to only calculate the December total and not in in the prior months.

Also, I'll update my profile, i have not been on here in years and did not think of that when I reactivated the account. As for your second question, I do not think there's a need to match Entity and Account, although we will have multiple entity that I'd use this solution for in the final version.

Again, really appreciate you taking the time to help me.

Terry

Great! Though I am confused how it is not working for December since the formula matches your expected results?

Post #3 and Post#5 December = $331,731

 

[tbobolz]

Great! Though I am confused how it is not working for December since the formula matches your expected results?

Post #3 and Post#5 December = $331,731

Oh, it appears I dropped the first part of the formula "=LET(s," by mistake and did not noticed. It appears to be working great!

Much thanks. Now i'll study it and learn from you.

Much thanks!

 

[dreid1011]

You're welcome and happy to help.