I am having a very odd problem with Excel 365 Pro+ formula results.
I have a complex cash flow modeling worksheet that includes 100+ columns of data in weekly buckets. The formulas in each column are circular/iterative and solve to a minimum cash balance, with the revolver paydown/drawdown balancing out the totals to achieve the goal.
There are multiple index match match formulas in each column that retrieve data from other tabs in the same worksheet and from external worksheets. The formulas work perfectly for about 30 columns then the results start to drift.
For example, the beginning cash for week two is a simple formula that is equal to the ending cash from week one (e.g. B1 = A20). But the two numbers depart after about 25 columns. For example, assume that the value showing in cell Y20 is 100. Thus, the formula in cell Z1 (=Y20) should return 100.
When you place your cursor in cell Z1 and press F2 and then F9 you see 100 as you should. However, the value actually showing on the worksheets for cell Z1 is 98. From that point forward, the difference between the two numbers looks like a sine wave from column to column. For example column Z is off by -2, column AA is off by+4 and column AB is off by -8 and so forth. The values double inversely all the way to the farthest column to the right.
I have tried all of the normal things; turning excel iteration up to 1000 with higher and lower precision variables, turning on Precision as Displayed, rounding the target numbers and the destinations numbers in the index match formulas. All to no avail, the result is always the same.
Ive been using Excel extensively for 30+ years and Ive never seen anything like it. Has anyone ever seen this type of error before and if so how did you solve it.
I have a complex cash flow modeling worksheet that includes 100+ columns of data in weekly buckets. The formulas in each column are circular/iterative and solve to a minimum cash balance, with the revolver paydown/drawdown balancing out the totals to achieve the goal.
There are multiple index match match formulas in each column that retrieve data from other tabs in the same worksheet and from external worksheets. The formulas work perfectly for about 30 columns then the results start to drift.
For example, the beginning cash for week two is a simple formula that is equal to the ending cash from week one (e.g. B1 = A20). But the two numbers depart after about 25 columns. For example, assume that the value showing in cell Y20 is 100. Thus, the formula in cell Z1 (=Y20) should return 100.
When you place your cursor in cell Z1 and press F2 and then F9 you see 100 as you should. However, the value actually showing on the worksheets for cell Z1 is 98. From that point forward, the difference between the two numbers looks like a sine wave from column to column. For example column Z is off by -2, column AA is off by+4 and column AB is off by -8 and so forth. The values double inversely all the way to the farthest column to the right.
I have tried all of the normal things; turning excel iteration up to 1000 with higher and lower precision variables, turning on Precision as Displayed, rounding the target numbers and the destinations numbers in the index match formulas. All to no avail, the result is always the same.
Ive been using Excel extensively for 30+ years and Ive never seen anything like it. Has anyone ever seen this type of error before and if so how did you solve it.
