Circular error

[EmmaTM]

Please help!

I have a spreadsheet of commissions worked out by employee depending on hours worked etc, this is driven by cell E3 having the net profit figure entered and then it calculates the total commission in G3
The entry I have from the accounts are as below, I wanted to be able to link E3 into P6 but I am getting a circular error due to P3 being the sum of G3, is there another way I can do this calculation?

Thank you

 

[rollis13]

To avoid the alert you could turn on the Iterative Calculations. Just go to the Options, open the Formulas section, enable the Iterative Calculations and set the Maximum Number to 1.
Here is some other information for Circular Reference: Link.

 

[Alex Blakenburg]

I can't say that I agree with @rollis13, turning on iterative calculations is really a last resort. It is an application wide setting and will impact all your open spreadsheets.
It will hide circular references.
What is the calculation for G3 (the commission) ? It is not normally based on a figure which is net of the commission you are trying to calculate.

 

[EmmaTM]

Thank you and sorry I had worded my request incorrectly, what I am looking for is a calculation where I can obtain the commission figure in P3 without getting the circular ref error.

 

[Alex Blakenburg]

Understood but what is the calculation ?

 

[EmmaTM]

Understood but what is the calculation ?

% P/M	Hrs Wkd	Total
0.20%	31.75	5508.08
0.45%	25.00	4337.07
0.20%	16.50	2862.47
0.20%	21.00	3643.14
0.20%	30.00	5204.49
0.20%	20.00	3469.66
0.20%	30.00	5204.49
		272064.62

This is a snippet of a few rows of data G3=I59 which is the total in the 3rd row.
The caluclation in the total row is =IFERROR(($E$3*G9/$D$3*0.45%),0)

Thank you

 

[Alex Blakenburg]

Can you provide an XL2BB. I can't tie your formula or schedule back to the original example.

 

[EmmaTM]

Commission.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1												1,851,302.42
2		Month		Full Time Hrs	Net Profit	Scheme Members	Total		Accrual Pot	Running Total		1,851,302.42	must match L1
3				34	1,452,225.00	N/A	101,629.05		53,791.05			101,629.05	Comm/bonus
4												1,749,673.37
5		**Check for Joiners/Leavers and Any Whose Hours Have Changed!**										297,444.47	corp tax
6												1,452,228.89	must match E3
7		EE Number	First Name	Surname	Emp/Dir	% P/M	Hrs Wkd	% Full Time	Total	Pay in Mth		3.89
8					E	0.20%	34.00	100.00%	6535.01	2904.45
9					E	0.20%	31.75	93.38%	6102.55	2712.24
10					D	0.45%	25.00	73.53%	4805.16	4805.16
11					E	0.20%	34.00	100.00%	6535.01	2904.45
12					E	0.20%	34.00	100.00%	6535.01	2904.45
13					E	0.20%	30.00	88.24%	5766.19	2562.75
14					E	0.20%	34.00	100.00%	6535.01	2904.45
15					E	0.20%	34.00	100.00%	6535.01	2904.45
16					E	0.20%	34.00	100.00%	6535.01	2904.45
17					E	0.20%	34.00	100.00%	6535.01	2904.45
18					E	0.20%	34.00	100.00%	6535.01	2904.45
19					E	0.20%	34.00	100.00%	6535.01	2904.45
20					E	0.20%	34.00	100.00%	6535.01	2904.45
21					E	0.20%	34.00	100.00%	6535.01	2904.45
22					E	0.20%	34.00	100.00%	6535.01	2904.45
23					E	0.20%	34.00	100.00%	6535.01	2904.45
24					E	0.20%	34.00	100.00%	p	p
25					E	0.20%	34.00	100.00%	p	p
26									101629.05	47838.00
27
Sheet1

Cell Formulas
Range	Formula
G3	G3	=I26
I3	I3	=G3-J26
L1	L1	='[MPI Budget to Sept 2024.xlsm]MPIL '!$Q$181
L2	L2	=L3+L5+L6
L3	L3	=G3
L4	L4	=L1-L3
L5	L5	=L4*0.17
L6	L6	=L1-L3-L5
L7	L7	=L6-E3
I8:I23	I8	=IFERROR(($E$3*G8/$D$3*0.45%),0)
J8:J23	J8	=IFERROR(($E$3*F8*H8),0)
H8:H25	H8	=G8/$D$3
I26:J26	I26	=SUM(I4:I23)

 

[EmmaTM]

The figures have changed because I have reduced the rows on my sample - thank you

 

[Alex Blakenburg]

Sorry I can't see any way of achieving what you are trying to do.
Where did your figure in E3 actually come from ?
I doesn't seem to make sense to base the commission/bonus calculation on a figure that is net of that same Commission/bonus especially when it is not a fixed calculation and is dependant on variable hours and variable % P/M