+ Reply to Thread
Results 1 to 8 of 8

CTC Calculation ended up with error

  1. #1
    Registered User
    Join Date
    05-11-2007
    Location
    Bangalore, India
    MS-Off Ver
    MS Office 2013
    Posts
    58

    CTC Calculation ended up with error

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: CTC Calculation ended up with error

    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?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    05-11-2007
    Location
    Bangalore, India
    MS-Off Ver
    MS Office 2013
    Posts
    58

    Re: CTC Calculation ended up with error

    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

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: CTC Calculation ended up with error

    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

  5. #5
    Registered User
    Join Date
    05-11-2007
    Location
    Bangalore, India
    MS-Off Ver
    MS Office 2013
    Posts
    58

    Re: CTC Calculation ended up with error

    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.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: CTC Calculation ended up with error

    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?

  7. #7
    Registered User
    Join Date
    05-11-2007
    Location
    Bangalore, India
    MS-Off Ver
    MS Office 2013
    Posts
    58

    Re: CTC Calculation ended up with error

    1. value of E3 should be (B3-T3)/12
    2. After E3 value is given, T3 value should be equal to B3

    E3 is blank and T3 is the sum of C3 to S3 (including E3)
    Balancing figure of E3 should be (B3-T3)/12 (with rest of other caluclation from C3 to S3)
    Once E3 value calculated, value of F3 increases
    M3 is calculated on F3
    once M3 is calculated, value of T3 will increase and it should match with B3

    At end the value of E3 is 1348.44...
    T3 value is 100000.05 (this can be round to 100000, value of B3 is 100000).
    Last edited by harishs; 06-28-2012 at 07:25 AM.

  8. #8
    Registered User
    Join Date
    05-11-2007
    Location
    Bangalore, India
    MS-Off Ver
    MS Office 2013
    Posts
    58

    Re: CTC Calculation ended up with error

    Dear Experts,
    Can someone help me on this query? let me know if you required any additional informations.

    I am reattaching the excel file and details here.

    1. value of E3 should be (B3-T3)/12
    2. After E3 value is given, T3 value should be equal to B3

    E3 is blank and T3 is the sum of C3 to S3 (including E3)
    Balancing figure of E3 should be (B3-T3)/12 (with rest of other caluclation from C3 to S3)
    Once E3 value calculated, value of F3 increases
    M3 is calculated on F3
    once M3 is calculated, value of T3 will increase and it should match with B3

    At end the value of E3 is XXXX...
    T3 value should equal to B3
    Attached Files Attached Files
    Last edited by harishs; 06-28-2012 at 11:29 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1