Resolve Circular Ref Issue

[kernel]

Hi,

Need some help to resolve my circular reference problem. I have a worksheet
where I am trying to model different scenarios with my mortgage.

Column A contains the interest rate ie
Column C contains the date (I have 1 row for every date between 20-may-08
and 30-jun-10)
Column D conatians my regular weekly and fortnightly payment amounts
Column E contains my mortgage balance formula =E37+D38-G38
Column F contains the daily interest calculation =(E38*A38)/366
Column G contains the monthly interest total calculation (it is always
charged on the 23rd of the month)
=IF(DAY(C38)=23,SUMPRODUCT(($C$4:$C$775<=C38)*($C$4:$C$775>DATE(YEAR(C38),MONTH(C38)-1,23)),$F$4:$F$775),0)

Any suggestions on how I can change the sheet to stop the circular issue
would be much appreciated.

Cheers,

kernel

 

[JLatham]

Your formula in G doesn't reference column E or D, so it looks to me like
(without testing) that you should be able to replace the -G38 portion of the
formula in E (presumably at row 38) with the formula you have in column G.
I'd wrap the whole thing in another level of parenthesis and give it a try?

So in E38 you'd have formula that looks like:
=E37+D38-(IF(DAY(C38)=23,SUMPRODUCT(($C$4:$C$775<=C38)*($C$4:$C$775>DATE(YEAR(C38),MONTH(C38)-1,23)),$F$4:$F$775),0))

As I said, untested, but it would appear to relieve you of the circular
reference issue.

 

[kernel]

Hi, thanks very much for taking the time to reply. I pasted your formula into
E38 but am now getting a circular in E38. I'm starting to think I may need to
resort to having 2 rows for every month date where the interest actually gets
summed and charged. So first row for the interest charge piece is the daily
interest calculation in column F on the balance in column G. The second row
for the same date would then just include the sum of the daily interest
values up to the previous charge date and no daily interest calculation.
Hopefully I can work out a way of doing this without 2 different rows
required. I think I need another gin!!!!.

Cheers,

kernel

 

[JLatham]

Sorry I steered you wrong. I need to take a little time and look at the
formulas that are giving problems and see how to resolve them. That'll take
me some time - busy schedule at the moment.