Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Circular reference error occurs when totaling a group of cells

0

Respected Members,

I am trying to SUM a group of cells (D2 to G2) to obtain the answer in H2.

However, every time circular reference error message pops up because I have used formulas in both D2 and E2 which is mandatory. In this case, I am unable to use SUM in H2.

Please help to resolve.

Thanks and Regards,

Akash Sharma

excel
posted by: Akash Sharma (rep: 40) on Tue Dec 1, 2020 at 10:27 am
Answer
Discuss

Answers

0
Selected Answer

Akash

You get a circular reference since the total in H2 includes D2 whose formula is 

=(H2*40%)
which refers back to the total H2.

To fix this, change D2 to 

=(12000*40%)
(a % of a fixed number) or some multiple of another cell, say =(G2*5).

REVISION 1: If the value of H2 is already known per employee (say an existing Annual Salary of 1,000,000 rupees) and the gross needs to be 40% of that, circular reference can be avoided by:

1) Copying the known salary to column J say (hidden in my file attached)

2) Changing the formula for gross salary in D2 to be 

=J2*40%
so 40% of a cell outside the range of the sum in H2 (=SUM(D2:G2) )

3) Adjusting cell E2, F2 or G2 so the sum in H2 is the same as J2. In my case I changed the formula for HRA in E2 to 

=J2-(D2+F2+G2)
  but it's your choice.

4) Once you've done that, you could copy column D (and others) and Paste/Values to fix the values. Column J could then be deleted.

posted by: John_Ru (rep: 6142) on Tue Dec 1, 2020 at 11:41 am
Discuss

Discussion

Hi request you to please suggest alternate solution. D2 (Basic Salary) is solely dependent on H2 (Gross Salary) wherein it has to be 40% of H2.

For around 3000 employees I cannot come up with fixed number because all calculations depend on H2.

Regards,
Akash
Akash Sharma (rep: 40) Dec 1, '20 at 12:06 pm
So do you already know the existing gross salary of each employee? Are you aiming to recalculate gross salaries or to reallocate that value between the column  headings in D:G? If so, is there a fixed ratio between D and E for all employees?
John_Ru (rep: 6142) Dec 1, '20 at 12:17 pm
See Revision 1 to my Answer. Note that to avoid circular references, the gross salary has to 40% of a value outside the range of the sum. That's how arithmetic works!
John_Ru (rep: 6142) Dec 1, '20 at 1:42 pm
Add to Discussion
0

It's not logical that you should know the gross salary before the base, even if that is the way they actually calculate. Following a strict logic you will know the base before the gross. Therefore the base must be set and the gross calculated, not the other way around. After entering the base salary as a hard number, the gross salary is calculated with this formula:-

=(D2/.4)+SUM(F2:G2)

or
=SUM((D2/.4)F2:G2)
posted by: Variatus (rep: 4889) on Tue Dec 1, 2020 at 6:30 pm
Discuss

Discussion

Agreed @Variatus. The eorkable options are yours (where the base salary is known) or my suggestion (where the gross exists and the base calculation  as a % merely reallocates a portion of the gross).

The illogical approach in the question (suggested by Akash's managers I suspect) leads inevitably to circular references.

I think we're done here! 
John_Ru (rep: 6142) Dec 2, '20 at 4:48 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login