+ Reply to Thread
Results 1 to 5 of 5

Circular References Error

  1. #1
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    35

    Circular References Error

    Whats up gang, post number 2.

    I'm working on a spreadsheet that has multiple formula's in multiple cells. I was able to get everything I need to work together using VLOOKUP and SUM and ROUNDDOWN... etc...

    However I am running into a very puzzling issue.

    All of the fields with the crazy formula's are in the I column.

    I'll list them here:

    I5: =COUNTA(C4:C331) - Gives me just a flat number return if a name exists in a cell

    I6: =SUM(E4:E29,I3) - Gives me the sum of the total value (weighted worth) of a deal a rep has brought into the company (Ex: 17.750)

    I7: =ROUNDDOWN(I6,0) - Gives me the pay tier level for their commission (Ex: Tier 17 pays $122 per deal)

    I8: =VLOOKUP(I7,A1:F33,6,FALSE)*I6 - Find the tier level and returns the amount paid per deal, then multiplies it by the weighted value of their deals (I6) (Gross Commission)

    I9: No Formula, Rep enters a number for how many times they have messed up on their timeclock

    I10: = I8*IF(OR(I5>12,I9=0),0,IF(I9=1,0.1,IF(I9=2,0.25,IF(I9=3,0.55,IF(I9=4,0.7,1)*IF(I9=5,1,1))))) - Reduces their commission by a percent based on how many timeclock violations they have accrued (I9)

    This is what's racking my brain right now. I have a cell where the rep can enter their hourly rate. (K3)

    K4: = K3*40 - To represent a 40 hour work week.

    I'm trying to illustrate two things from here.

    I4 - Gross Pay - Easy right? Take their gross commission (I8) which is either their full commission or a reduced amount if they have timeclock violations. And add (K4)

    SO: I4 = I8+K4 - This works fine.

    The LAST THING i'm trying to illustrate

    Actual Pay. Their actual commission from I11 (which will show the full commission or a reduced amount) + their base pay (K4). Which gives me I3=I11+K4

    Here is what is confusing the hell out of me.

    For some reason, if I use a random cell to run that formula, it works just fine. If I use I3 to run that formula, I get a circular references error with blue lines and arrows traveling up and down the I column. I3 is not being used in any formula, anywhere.

    It works JUST FINE in any other random cell. I'm so confused.!


    1.JPG
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    35

    Re: Circular References Error

    This is what it looks like when I try the same formula in K10 and I3

    1.JPG

  3. #3
    Forum Guru
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    7,321

    Re: Circular References Error

    I3 is used in cell I6.
    Please Avoid Joining My List Of Blocked Users by:

    Saying Please and Thank you.

    Making requests not demands.

    Checking back on your post. I will not edit any post after 4 days.

    Marking threads as closed once your issue is resolved. How? The tools at the top

    Any reputation (*) points appreciated. None of us gets paid here.

    If you found someone's input useful, please take a second to click the * at the bottom left to let them know

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    11,856

    Re: Circular References Error

    I3 is not being used in any formula, anywhere.
    Unless you have made a mistake in the formulas you posted (it can be difficult to tell from a picture), I3 is used in I6. I3 feeds into I6, I6 into I7, I7 into I8 and I10, and, I assume since you don't post I11 formula, I11=I8-I10, then you want I11 to feed back up into I3. Definitely a circular reference. Either including I3 in the summation in I6 is incorrect, or you need to think through your logic more carefully to eliminate the circular reference, or you need to think through your logic to see if a circular reference is necessary for the calculation being performed and be sure to program the spreadsheet to correctly use the circular reference.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    01-05-2016
    Location
    Novato, CA
    MS-Off Ver
    2010
    Posts
    35

    Re: Circular References Error

    Mother of christ....

    mehmetcik called it. This sheet by modifying an old one to reflect the new company commission structure.
    Last edited by TyColt; 01-07-2016 at 08:45 PM.

+ 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