Getting circular reference when using If statement precending a workday formula

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
While trying to create a formula that says if a certain field is blank, return blank, otherwise return the date for the workday formula. Here's the formula I'm using... I'll list two as I am pulling the workday less 1 business day, then less two business days, then less 3 business days, etc... all the way out to less 60 business days.

Formula to show today's date if field Q29 doesn't equal 0: =IF(Q29=0,"",WORKDAY(TODAY(),0,$S$2:$S$13)) The field I'm using this formula in is C31.
Formula to show last business day's date if field Q30 doesn't equal 0: =IF(Q30=0,"",WORKDAY(TODAY(),-1,$S$2:$S$13)) The field I'm using this formula in is C32.

I keep getting circular reference error when I try to execute this formula with the If statement included. Attaching my circular reference error, I am unable to figure out what the circular reference is.
Circular Reference Error.PNG
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
Do you have any formulas in cells Q29:Q30 or S2:S13?
If so, what exactly are they?
 

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
Yes, the formula in Q29 is =SUM(D29:P29).
The formula in Q30 is =SUM(D30:P30).
There are no formulas in S2:S13, that is my list of holidays.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
Okay. Perhaps I should tackle this differently, I am getting the circular reference error. I know it's because I'm using fields with formulas in them as part of my formula. I need to know how to get around this.
Since field Q29 has a formula in it to sum fields D29-P29, then I'm using the sum of field Q29 to calculate the results of my formula in field C20, perhaps I could tackle this differently. How can I create a workday formula that only calculates the date if the sum of fields D29:P29 doesn't equal zero. If the sum of fields D29-P29 is anything other than zero, then I want to calculate the date. Here's what I have, but it's not working...
=IF(SUM(D29:P29)=0,"",WORKDAY(TODAY(),0,$S$2:$S$13))
Where D29 through P29 have formulas in them to calculate their totals.
The formula I have in D29 is... =IF($C29="","",COUNTIFS('ALM Export'!$H:$H,">"&$C30,Summary!I:I,D$2))

So my real question is this... I have come up with this formula =IF(SUM(D29:P29)=0,"",WORKDAY(TODAY(),0,$S$2:$S$13)), but I don't think it's working because D:29-P29 are fields that their totals are calculated by a formula as well. So how can I fix this formula so that it allows me to calculate the sum of fields D:29-P:29, even though D:29-P29 are using formula to display a total. Then if the sum of D:29-P29 is 0, display blank, if the sum of D:29-P29 totals more than zero, then do the workday formula?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,703
Office Version
  1. 365
Platform
  1. Windows
So my real question is this... I have come up with this formula =IF(SUM(D29:P29)=0,"",WORKDAY(TODAY(),0,$S$2:$S$13)), but I don't think it's working because D:29-P29 are fields that their totals are calculated by a formula as well
No, that is not the problem. Excel has no problem with formulas being based on other formulas.

Do you understand what a "circular reference" is? It is when a formula either directly, or indirectly calls itself. You cannot do that without using "iteration" or VBA.

An example of a direct circular reference would be something like this:
In cell A1, you have the formula
Rich (BB code):
=SUM(A1:C1)
So the formula in the cell actually contains a reference to the cell that the formula is contained in (A1)! Excel is not sure what to do with that.

An example of an indirect circular reference might look like this:
In cell A1 is the formula:
Rich (BB code):
=B1+C1
and then cell C1 contains the formula:
Rich (BB code):
=A1+10
So the formula in cell A1 is indirectly calling itself (by way of cell C1).

What Excel is telling you is that you have one of these situations somewhere on your worksheet. Most likely, it is an indirect circular reference (or else it would probably be obvious to you).

Did you try following those steps I posted in my previous post?
That should help you identify exactly where on your worksheet this circular reference is happpening.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top