Share via

Why OFFSET Formula gives a circular reference ?

Anonymous
2023-11-09T20:24:19+00:00

Hi, Could you please help me what/how exactly this formula means/works and why this formula gives a circular reference if there is no cross reference to even part of it? Basically it should calculate a financing fee amortisation (5 years x 12 months) = 60 periods, where G402 cell is the 5 year & PlanH10 is the 12 months & J422 cell is the finance fee amount to be amortized & count I4-all is the periods running. FORMULA IS: =IFERROR(SUM(OFFSET(J422,,,,-MIN($G$402*Plan!$H$10,MAX(COUNTA($I$4:J$4),1))))/($G$402*12),"")

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Ashish Mathur 102K Reputation points Volunteer Moderator
2023-11-09T23:13:48+00:00

Hi,

Please share some data, explain the question and show the expected result.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-11-10T07:14:50+00:00

    Dear Ashish !

    Thanks a lot for taking this !

    I realized after closing another external file (which was parallelly opened with my file),

    that the external file had the circular references problem (not mine) and

    that made excel say funny / irrelevant circular references in my own file.

    After closing the external file, my own file does not show any more the circular references.

    Thanks a lot, Gyula

    Was this answer helpful?

    0 comments