Excel "circular reference" problem

I've tried to put some formulae in for a table and logically in my head it makes sense but Excel is freaking out because of a "circular reference" that should be irrelevant. Any solutions would be appreciated.

I have a table with dates for a fortnight in columns and rows for employees. In one row for each date I have a sum of money to pay as a bonus for everyone who worked that day. In another row I have a total number of employees working each day. In a final row I then have the bonus divided by the number of employees working.

In the table of dates/employees then I am putting for anyone who worked a link to the calculated average bonus.

For the number of people working I want to replace the static number with the Counta formula covering all possible employees for that date. If they worked then the cell isn't blank.

Excel is freaking out about the Counta formula though as its then a circular reference, except counta is just meant to be a check if its blank or not, the rest of the circle is irrelevant. Eg if I put the counta formula into another row and then copy and paste values onto the row I'd like to put the formula into then it works fine.

Is there a solution here? I suppose I could duplicate the table and type 1 if someone worked and sum the 1's then have the output of the results in a duplicated table but I'd rather not duplicate it unless absolutely necessary and even then it would still ultimately be circular, even if its irrelevant.

I don't see the problem from your text. But maybe you can write the formulas you are using. One example would be enough.

OK taking a quiet Monday example:

D2= "26.08"

D10, D11, D15, D17, D19, D20, D21 and D23 = "=D31"

D30 = "8" (everything works) want to replace this with D30 = "=counta(D3: D25)" (everything breaks, space to avoid smiley)
D31 = "=INT(D2/D30*100)/100" (correct result = 3.26)

If I put the counta formula into D30 then Excel freaks out, complains about the circular reference and doesn't produce the results. If I do the following and then copy and paste values row 32 into row 30 it produces the right results so it ought to work somehow.

D32 = "=counta(D3: D25)" (produces 8 as the correct answer) (space to avoid smiley)

There's an option in the excel options menu under formulas to allow circular references. Enable iterative calculation.

Ok I think I get it. You can try what Waith said. Other solution below.

Quote:

---

Originally Posted by RandBlade

Is there a solution here? I suppose I could duplicate the table and type 1 if someone worked and sum the 1's then have the output of the results in a duplicated table but I'd rather not duplicate it unless absolutely necessary and even then it would still ultimately be circular, even if its irrelevant.

---

I don't think you need another table, you need just another column. A row where you put a 1 for each one that works. And your column with the bonus of each employee would than be

D10, = "=IF (E10=1, D31, 0)"
D11, = "=IF (E11=1, D31, 0)"
D11, = "=IF (E12=1, D31, 0)"

Where E is that extra column.

BTW you can disable smiles in the advanced editor or use the code code.

That worked thanks.

Out of interest, which of the solutions? Or both?

Your one (rather than changing settings). Didn't try changing settings

OK good, I haven't tried it myself. :)

It's a bit more elegant solution i think, I like to keep the cells you input yourself untouched by excel, to prevent accidental entry there. To keep it tidy I'd probably put it in a different worksheet (one worksheet where you enter who worked and what the bonus is, one with the bonuses per day).

I think I'm going to do that Flixy (as I said sort of two tables as the solution) as that way I can highlight the whole table and delete in order to enter the new figures, rather than delete every other column. Especially as I'm most interested for payroll's sake in the total bonus per person rather than the daily per person then I can hide away the calculations table.

Quote:

---

Originally Posted by RandBlade

I think I'm going to do that Flixy (as I said sort of two tables as the solution) as that way I can highlight the whole table and delete in order to enter the new figures, rather than delete every other column. Especially as I'm most interested for payroll's sake in the total bonus per person rather than the daily per person then I can hide away the calculations table.

---

Do you have a table where hours are entered? Could you use that? External workbook references work if you need them.

Quote:

---

Originally Posted by Wraith

Do you have a table where hours are entered? Could you use that? External workbook references work if you need them.

---

Good idea but no as the clock on/off system is accessed online and I take a total from that. Though personally I've never used external workbook references preferring to make my workbooks extra-complicated instead if need be as external references tend to be of higher risk of getting corrupted/broken links in my experience.

Is it backed by an accessible database of any kind? You could pull a query table from there, if so.

You can also press Alt F11, create a module, and then a custom function that makes calculations.

Example:
I am creating a function like f(x,y) = x+y+1 in the module

Code:

---

Function f(x as Double,y as Double) as Double
f = x +y +1
End Function

---

Then in cell C2 I add the custom formula that uses cells A2 and B2

Code:

---

= f(A2,B2)

---

This requires some coding, but could make your life easier. Just make sure you save as XLS, XLSB or XLSM. If you save as XLSX no code is saved. This approach is useful when you need to do complex processing and you do not want mistakes in the formula. This approach will never give you circular references and you eliminate the need of additional columns.