Iterative Calculation #VALUE Error

[jewkes6000]

I have created a spreadsheet which purposely has a circular reference. To avoid issues, I turned on the "iterative calculation" function in the Excel options. I've included the file to download below. The formula giving the error happens in Column AA, Column AG and Column AM and goes all the way down starting at row 29. I have it set up so that if I change the value in the UoM column (two columns to the left; so in column AG, that would be column AE) to TCC or TC or TFC, then the formula calculates a percentage on the overall cost. This cost is part of the overall cost which is why it's a circular reference.

ANYWAYS, how I have it set up does in fact work. You can see in the Phase 1 and Phase 3 groupings in my spreadsheet, they currently work. BUT every so often (at least once a day or more), all of the rows which I've set to TCC, TC or TFC in the UoM column suddenly start giving the #VALUE error. I can fix the problem by changing all of the UoM values back to LS which then makes the error go away, and then simply change them back to the necessary units (TCCC, TC or TFC). The problem is that sometimes I have 10 to 15 rows which I have to do this with and it's cumbersome.

In the attached file, you will see that the Phase 2 grouping is currently giving an error. Yes, you can fix it by doing the above, but I am looking more for a permanent fix to stop this from happening at all.

This one has been driving me crazy and I can't find a fix! Any help is very much appreciated.

Iterative Calculation Error.xlsm

drive.google.com

 

[jasonb75]

It would be better if you posted a condensed sample (10-20 rows with only the columns relevant to the question) directly to the forum using XL2BB (link in my signature below). Many members are not able to download linked files due to security restrictions.

The #VALUE! error usually occurs when you attempt to perform a mathematical operation on a text string, the simplest example would be =A1+B1 where either of those cells contains a formula that uses "" to return a blank.

 

[jewkes6000]

@jasonb75 - I tried downloading the XL2BB and everything seemed to work until I went to "turn on" the add-in. The box is checked; however, the add-in doesn't appear in my ribbon.

So I did what you said and condensed the spreadsheet, but I simply uploaded to my Google drive account and you can download it below.

Iterative Calculation Error2.xlsm

drive.google.com

 

[Fluff]

The box is checked; however, the add-in doesn't appear in my ribbon.

Have you unblocked the add-in?

• Find the Add-In file in Windows File Explorer
• Right click - Properties
• On the 'General' tab, is there an 'Unblock' box at the bottom?
• If so, tick that box then click OK

 

[jasonb75]

So I did what you said and condensed the spreadsheet, but I simply uploaded to my Google drive account and you can download it below.

I also said

Many members are not able to download linked files due to security restrictions.

Which would still apply regardless of the size of your file.

 

[jewkes6000]

@Fluff - That did it. It worked

@jasonb75 - Here is what I got from the XL2BB

Qty	UoM	Unit Cost	Total ($$$)	Cost per A1 GSF		Qty	UoM	Unit Cost	Total ($$$)	Cost per A2 GSF		Qty	UoM	Unit Cost	Total ($$$)	Cost per A3 GSF
	LS	280,000.00	280,000	24.03			LS	420,000.00	420,000	20.95			LS	200,000.00	200,000	99.90
	TCC	0.50%	876	0.08			TCC	0.50%	#VALUE!	#VALUE!			TCC	0.50%	22	0.01
	LS						LS						LS
			876	0.08					#VALUE!	#VALUE!					22	0.01
	LS						LS						LS
			280,876	24.11					#VALUE!	#VALUE!					200,022	99.91
	TCC	4.50%	7,888	0.68			TCC	4.50%	#VALUE!	#VALUE!			TCC	4.50%	195	0.10
	TCC	5.00%	8,764	0.75			TCC	5.00%	#VALUE!	#VALUE!			TCC	5.00%	217	0.11
	LS	2,500.00	2,500	0.21			LS	2,500.00	2,500	0.12			LS	2,500.00	2,500	1.25
	TCC	4.00%	7,011	0.60			TCC	4.00%	#VALUE!	#VALUE!			TCC	4.00%	174	0.09
11,651	GSF	0.50	5,826	0.50		20,049	GSF	0.50	10,025	0.50		2,002	GSF	0.50	1,001	0.50
	LS						LS						LS
	TCC	1.30%	2,279	0.20			TCC	1.30%	#VALUE!	#VALUE!			TCC	1.30%	56	0.03
	TCC	1.50%	2,629	0.23			TCC	1.50%	#VALUE!	#VALUE!			TCC	1.50%	65	0.03
	TCC	3.00%	5,258	0.45			TCC	3.00%	#VALUE!	#VALUE!			TCC	3.00%	130	0.07
			10,166	0.87					#VALUE!	#VALUE!					252	0.13
	TCC	3.80%	6,661	0.57			TCC						TCC
3,328	SF	38.00	126,464	10.85			SF						SF
			133,125	11.43					-	-					-	-
			175,279	15.04					#VALUE!	#VALUE!					4,338	2.17
			175,279	15.04					#VALUE!	#VALUE!					4,338	2.17
			175,279	15.04					#VALUE!	#VALUE!					4,338	2.17
Qty	UoM	Unit Cost	Total ($$$)	Cost per A1 GSF		Qty	UoM	Unit Cost	Total ($$$)	Cost per A2 GSF		Qty	UoM	Unit Cost	Total ($$$)	Cost per A3 GSF
			280,876	24.11					#VALUE!	#VALUE!					200,022	99.91
			175,279	15.04					#VALUE!	#VALUE!					4,338	2.17
			456,155	39.15					#VALUE!	#VALUE!					204,360	102.08
			456,155	39.15					#VALUE!	#VALUE!					204,360	102.08
			456,155	39.15					#VALUE!	#VALUE!					204,360	102.08

 

[jasonb75]

I couldn't find anything from that but I was able to download your attachment on a different computer with more flexible security settings.

I've looked over the formulas and can see nothing standing out as a definite cause. Tracing the precedent cells of the first error in the sheet, I found that there are various SUBTOTAL formulas also with errors that appear to be forming part of the circuit.

I've tried changing SUBTOTAL to AGGREGATE and that has cleared the errors, although whether that is a permanent solution or a fix that will revert back to errors later remains to be seen.

As you have a lot of SUBTOTAL formulas in the sheet, the quick way that I used was find and replace.

First of all, make sure that the sheet is not protected and that only a single cell in the sheet is selected.

Press Ctrl h to bring up the find and replace window.
In the Find what: box, enter SUBTOTAL(9
In the Replace with: box, enter AGGREGATE(9,2
Make sure that the 'Match case' and 'Match entire cell contents' boxes are not checked (click on the Options>> button in the bottom right corner of the pop up box if these are not visible).
Click Replace all.

A message box should inform you how many have been replaced, then the sheet should recalculate.

AGGREGATE(9 and SUBTOTAL(9 are effectively the same, the additional option 2 with AGGREGATE tells the formula to ignore errors as well which, depending on the origin, may possibly prevent them from occurring to begin with.

 

[jewkes6000]

@jasonb75 - Thank you the tips. I will try the Aggregate function and see if it helps.

 

[jewkes6000]

@jasonb75 - I'm still in the early phases of testing, but so far, the Aggregate formula is not producing errors. I'll keep my fingers crossed. Thank you again!

 

[jewkes6000]

Almost a month into it and still no errors with the Aggregate formula. Seems to be a workable solution. Thank you again!