Forum

Notifications
Clear all

Problem with circular reference

7 Posts
3 Users
0 Reactions
86 Views
(@usb)
Posts: 244
Honorable Member
Topic starter
 
Hi,
In the attached file in column H there is a calculation based on column G.
When I drag the formula in column G to the following rows, an error message appears -
 what can be done??
I will note that in my complete file in column H there are more complex calculations based on column G...
 
Posted : 21/06/2023 8:25 pm
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

Currently Column G conditionally sums values from Column H. In turn, Column H depends on the value in column G in cases where the second IF is evaluated.

What exactly do you want to see in columns G and H?

 
Posted : 22/06/2023 12:57 am
(@usb)
Posts: 244
Honorable Member
Topic starter
 
Column G should summarize the weekly hours for the current week.
In column H he calculates the total daily hours 100% -
 checks if it is Friday and also 42 minus the cumulative hours is less than 7 = then calculate 42 - the cumulative hours.

The question is if there is a way to tell Excel not to get stuck with the circular reference
 or if I will have to think of another solution in the calculations?
 
Posted : 22/06/2023 1:31 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

I'd say you need to find a different calculation. Perhaps the attached file helps you on your way. If not please clarify your intentions. You still haven't mentioned why you reference cells in column Q, 10 rows down from the current row. And what happens if that value in Q is not less then 7? Currently, the formula returns FALSE as there is no 'value_if_false' declared in the IF statement.

 
Posted : 22/06/2023 7:32 am
(@usb)
Posts: 244
Honorable Member
Topic starter
 
The formula in column H is less important to me, because in my file it is a very complex formula.
My problem is that I need to summarize in column G the hours from column H.
In the attached file I saw that you summarized the hours from column F.
Need to summarize from column H
But then there is a problem with circular reference
Any idea for a solution?
 
Posted : 22/06/2023 8:07 am
Riny van Eekelen
(@riny)
Posts: 1195
Member Moderator
 

No! Sorry.

 
Posted : 22/06/2023 11:00 am
(@keebellah)
Posts: 373
Reputable Member
 

The strange thing is tha every time you refresh the formula the circular reference appears in a different row, if varies.

 

Another thing I don't get is why H35 adds upo 208 and if the H2:H32 is a named range it adds to 0

Many strange things happen in this file

 
Posted : 23/06/2023 2:19 am
Share: