CTC Calculation ended up with error

[harishs]

Hi Experts,

I was calculating CTC and I ended up with an error #NUM/Circular Reference. I have attached the excel file for your reference.

In the attached file,
Column B is my actual CTC on which I want to do a breakup (split CTC into salary components).
Column T is final CTC, which should match with Coumn B (plus or minus 12rs is OK (can increase or decrease)).
Column T is sum of Column C to S
Column E should have the balancing figure of Column B - Column T

How do i get a value in Column E without an error? Please help me.
Thanks in advance.

Regards,
Harish S

[Richard Buttrey]

You can't use a formula since you have dependent cells in a circular reference.

One option would be to use a 'Worksheet_BeforeDoubleClick' event macro but why should E4 be 1348.44 if as you say its value is B4-T4?

[harishs]

Dear Richard,
E4 should be the balancing figure of B4-T4.

I have manually keyed-in 1348.44 which is the balancing figure of (B4-T4)/12. E4 is monthly component hence divided by 12.

Regards,
Harish S

[Richard Buttrey]

Sorry I still don't understand

B4 is 100,000
T4 is 100,000.05

Hence (B4-T4)/12 is -0.0039 and not 1348.44

[harishs]

Dear Richard,
Value of E4 is manually calculated (outside the excel) and keyed in there for better understanding...

Let me elobrate the calculation. I will explain you the calculation which are in row 3.
Here,
E3 is blank and T3 is the sum of C3 to S3
Balancing figure of E3 should be (B3-T3)/12
Once E3 value calculated, value of F3 increases
M3 is calculated on F3
once M3 is calculated, value of T3 will increase and E3 value automatically changes (should decrease).

How will i get value of E3 automatically with a code or formula.

[Richard Buttrey]

So we're talking about row 3 not row 4.

What do you expect the value of E3 to be and how are you calculating that?