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