Circular References Error

[TyColt]

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

[TyColt]

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

1.JPG

[mehmetcik]

I3 is used in cell I6.

[MrShorty]

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.

[TyColt]

Mother of christ....

mehmetcik called it. This sheet by modifying an old one to reflect the new company commission structure.