Tracing a Circular Reference in Custom Formula

[downforce]

Note: This question is posted on another forum

http://www.ozgrid.com/forum/showthread.php?t=77553

I'm working on creating a custom formula that loops through each row of data and performs a range of calculations when a common number appears in a field (payroll number).

The formula is being applied to 2 sets of data, both are similar however 1 has a few more fields.

When I use the custom formula in the 2nd dataset I keep getting a Circular Reference.

I have attached a stripped down version of the whole spreadsheet, which just relates to the part causing the problems.

On the sheet 'Teaching (Yr1)', the calculation works fine.
On the sheet 'Support (Yr1)', the calculation doesn't and Excel prompts with a Circular Reference.

I've tried using the Circular Reference toolbar to trace the predecessors, however it doesn't highlight what they are. All the inputs are blue, and none of them perform any calculations on the cell/column with the formula in it (unless I'm being exceedingly blind!)

Excel gives the following message ...

Excel cannot calculate a formula. There is a circular reference in an open workbook, but the references that cause it cannot be listed for you.

I've spent many hours (probably 10+!) debugging the formula trying to ascertain why it's happening and I'm stumped! I've run through all the named ranges, I've changed the formula and still no success.

What I have noticed though, that for some reason the same formula is 'called' multiple times, in fact 3 to be precise.

When I enable Tools -> Options -> Calculation -> Iteration, the formula calculates, however whenever the spreadsheet does an full calculation, it reverts back to #VALUE!.

Any assistance would be much appreciated! In fact I'm at the point where I'll pay for assistance.

[FrankBoston]

I downloaded your example. I saw many #Value errors, but no circular reference errors.

A #Value error is caused by supplying an argument of the wrong datatype to a function, where Excel cannot convert the datatype in a reasonable way. For example, supplying a String when the function argument is a Range, or vice versa.

Your function has so many arguments, I gave up looking for the cause.
Also, the arguments go through defined Names, so it is even more complicated.

Here are my impressions, as unhelpful as they may be.

Functions with 20 arguments are usually trying to do something that should be done on the worksheet with worksheet formulas, even if you need to place the "helper" columns far to the right, or hide them. This breaks down the calculation into manageable pieces. Try to use User Functions to do something with small pieces of data where a complicated formula must be used, or to simplify your use of worksheet functions.

You are using many Defined Names to refer to worksheet columns.
They are all "global" names, and you have added endings like "_t1", "_s1", and "_yr1" to keep them separate. Defined names have hard to find rules.

One rule that would help you is to define your names as "local" names
Defining "dog" as [=Sheet1!A1] makes dog global, having that value in all worksheets.
Defining "Sheet1!dog" as [=Sheet1!A1] and "Sheet2!dog" as [=Sheet2!A1]
makes dog have the value [=Sheet1!A1] on Sheet1, and the value [=Sheet2!A1] on Sheet2.
So, cell ranges with the same "meaning" on different sheets can have the same name.

Giving every column a defined name helps if you need to change the column structure, and you are referencing columns within functions. It hurts understanding when the names are numerous and long.

If you use more worksheet formulas, you need to use fewer defined names, because the column references remain correct when adding or deleting columns.

[downforce]

Thanks for the helpful reply, much appreciated!

Naa wasn't unhelpful, in fact it has confirmed what I was starting to think that I need to split it out more.

In terms of the Circular Reference, if you Edit/Enter the cell 'Support (Yr1)'!AL8 then it re-creates the circular reference. It's all very temperamental.

I think I've tried to make it too swish and in the end have made it overly complicated.

With the #VALUE! error, I've gone through it and all the ranges are ranges, however some of the strings may be interpreted as a number type (Integer/Single etc), hence causing that problem.

Looking at what I'm trying to solve, the problem I've had is there are 2 unique criteria, the Payroll Number and the Month in Post with which I want to perform calculations.

The only way I know to pull these together is an Array formula. I previously had a problem using it due to row being added/deleted and the range in the formula, however I've re-discovered Dynamic ranges and can use these for the each of the ranges, so I can move it back to a Worksheet Function.

Still, I'm going to need 3 columns for each month, yuck 36 columns!


Thanks for the tip on the Named Ranges. I couldn't figure it out myself how to add a Named Range for the current sheet only and have never bothered to look into it.

I will definitely change them over to local where required. Some of them need to be global as they feed into other sheets.


OK, back to the drawing board. Not one to be defeated by a problem, I'll come back to another time, at the moment I'm under a deadline to get this whole spreadsheet finished!

Hate being 'beaten' by Excel!

[FrankBoston]

The typical way to combine two criteria without using explicit array formulas is with SUMPRODUCT. For example

Please Login or Register  to view this content.

This runs through the range 5:80. Where the conditions are false, 0's are multiplied into the result. Where they are both true, 1's are multiplied, selecting only those values in C5:C80 that are wanted.

Of course, the values "ID23" and "1" can be names of cells holding those values.

The --(boolean) is a standard trick to change True|False to 1|0

[rylo]

Hi

What are you trying to achieve with your for loop in the function calcMonthCost.

With the formula as it stands in the example you are in row 8.

The for loop is cycling through the entries in column C.

1) You are ignoring the first 6 rows.
2) You examine C7 to see if it is 2. It isn't so ignore
3) Row 8 is the current row, so you ignore it.
4) You then look at C9:C12 to see if it matches the value in C8 then go off to other functions.

What are you trying to achieve with this?

rylo

[downforce]

What are you trying to achieve with this?

Thanks for responding.

I am trying to find the total monthly payroll cost (for the employer).

The payroll cost is made up of: Basic Pay + NI + Super.

Basic pay for teachers is the based on spine points + various allowances (TLR/SEN/RNR/Safeguarding) + any 'other' payments (be it bonuses or other one-off payments).

NI is calculated on the Basic Pay for all jobs for that month. NI is calculated on a 'stepped scale' (mental blank to the real phrase used) based on the total monthly pay.

Therefore what I'm trying to achieve is calculate the basic pay for each job for each person (i.e. Payroll Num ColC). Add each of the basic pay for each jobs together then calculate the NI. Lastly it then apportions the NI based on the % of the current row's pay as a % of the total monthly pay for that employee.

Based on FrankBoston's post I was leaning towards creating 3 sets of columns, 1 which calculates the Basic pay for the current row (spine point + allowances), then a 2nd which has an array formula which performs the NI calculations (based on Payroll Num =2 and Month In Post = Y) ,and then and does the apportionment and a 3rd which gives the total based on sum(Basic,NI,CalcutionOfSuper) which is presented to the user.

[rylo]

Righto...

So using the error cell (AL8), can you manually calculate what the result should be, and exclain how that calculation was made.

If there is anything specific to column AL (I seem to recall Apr was a branch in the function), then do the same thing for another cell in the same row and explain how that calculation is made.

Use direct cell references (rather than range names) to make things clearer...


rylo

[downforce]

If you turn on Iteration, it calculates the correct result (I used the default Iteration settings).

The reference to Apr was whether or not to look up a value (GTC). In the attached spreadsheet I've stripped out a reference to a settings cell on another Worksheet which the user specifies the GTC month (it's usually Apr but can be May or even June) and replaced it with the text "Apr".

As far as I can tell, there is nothing specific to column AL. I've even gone to the extend of deleting every column that's not required, with only the Support sheet in a workbook. Same problem occurs.

I will try redoing it with direct cell references, rather than named ranges.

[rylo]

Hi

Bit more for you. The function call

Please Login or Register  to view this content.

seems to be causing the problem.

I changed curcell to be a long, made the appropriate changes in the function fcostforeachrow(), (had to manually make the offset items related to a specific cell for the point of the exercise), then in BG8 put in the formula

=fCostForEachRow(8, 1, AL6,U8, 2, C8,D8, F8, K8, Q8, R8, AH8, AI8, AJ8, 8, "Support", 1)

I then commented out the function call above and put in the line

Please Login or Register  to view this content.

in the select statement for case "Support"

and it didn't bring back a circular reference.

I haven't worked out why yet but the curcell reference (ie to itself) keeps it into a loop. I also haven't worked out why it works in Teaching, but fails in Support.


rylo

[downforce]

*does the biggest happy dance of all century!*

I figured it out ... purely by luck. Was copy/pasting parts of fCostForEachRow to my new version based on multiple columns ... and noticed something ....

The 2nd last line says:

Please Login or Register  to view this content.

But the GTC Column is an optional variable so iGTCcol is will come back 0.

I changed it to:

Please Login or Register  to view this content.

AND IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!

Nevertheless, you've given me some goods tips to make it cleaner/neater so I will still implement them.

Thanks for all your help!

*goes off to continue his happppyyyy ddaaaannceeee*