SumProduct failing after row 4789

I use SumProduct copiously throughout my spreadsheet, and for earlier rows it works fine. But from Row 4789 it fails.

If I manually re-type in the formula, it works; but if I dopy it down (from the previous row(s) it does not - although the formula is still in the cell, the numerical value is that of the row above. This repeats since (to row 4795 so far).

I have attempted to create an abbreviated spreadsheet of just the last dozen rows or so, but cannot. I do have a link to my whole spreadsheet in DropBox: https://www.dropbox.com/s/u128eu2uxc...ards.xlsm?dl=0 if that helps.

For example: =SUMPRODUCT($AH$2:$MT$2,$AH4789:$MT4789)*17.2+(MV4789+MW4789)*17.2 sows the previous row's number

But =SUMPRODUCT($HE$5:$HY$5,$HE4789:$HY4789)*38.9+MX4789*38.9 works fine

I'd appreciate some ideas on how to trace this fault.

Contributors are unwilling to go to file-sharing sites so please post file here. If the file is large then "ZIP" it.

This is odd. i posted a reply a few minutes ago - and it has disappeared! So I'll try again.

Thanks for taking the trouble to get back to me John.

Alas, my spreadsheet is still too big when zipped - 153MB. I knew this would be a problem, which is why I hoped someone may be able to suggest a method to fault-find this problem.

You have circular references in ML2:ML9, column RK and column RL - take care of these and it should resolve your problem.

Thanks Joseph (I thought I'd already said that a couple of days ago, but cannot see it here, so maybe my attempt at posting failed).

I did correct that somewhat glaring error; now corrected. I thought that was it, but also no.

This SumProduct function in my spreadsheet is behaving very oddly. Having worked well in thousands of instances, this last few days it no longer does, at least as heretofore.

I normally just copy one day's results down to the next row, incrementing the date. Which worked fine, until now. So since Row 4793, the copying (working fine for all other columns) just repeats the preceding value rather than doing the SumProduct calculation anew.

But it gets stranger. If I cut and paste the cell, the new value is correct - just not if I've copied the cells downwards. The cells on the Raw Data sheet (eg: B4795) are themselves copied from the NormData sheet, wherein the SumProduct is generated. I have cut & pasted each of the five SumProduct cells in NormData B4797 - F4797, wherein the correct value is shown.

I've posted an updated version of my spreadsheet: https://www.dropbox.com/preview/Life...?role=personal

Any suggestions as to why this may be happening would be appreciated.

I've Error Checked every sheet; all is seemingly well.

Thanks for the reminder AliGW. I did know that. I've tried making a reduced spreadsheet with the problem, but it only seems to exist in the full one - all the previous 4,000+rows are fine (so copying from a small set works oK).

But I appreciate that this means fellow Excel Forumers probably cannot help. I just hoped that someone knows about this problem and can offer suggestions to explore.

@menem, if you change the end of the link from 0 to 1, then it will automatically download

Alas, the zipped file is little smaller - 165MB down to 153; so still way too large I'm afraid.

I understand toachan. But it is what it is. My file is huge because my data and analyses are - 4,500 daily records of 600 variables and dozens of sheets. I tried to reduce the file size without compromising the problem issue but could not. Apologies.

Perhaps this link is better? https://www.dropbox.com/scl/fi/ppznu...ccrnzedlpjmwmp

My apologies for not always doing things correctly.

Which tab?

To John Toley: Which tab?:

The NormData sheet, Rows 4792 onwards, cells B4792 - F4792, and downwards.

Cutting and pasting back in to each of these cells does seem to make the SumProduct function work, so that would be a work-around temporarily. I left these more or less as-is, so you could see the problem.

You'll see that all the preceding copy-downs up to 4792 worked as expected.

Thanks menem for looking at my problem; and your suggestions and query.

You have picked up my mis-typed entries in MB; it should have been MB1015 to MV1014, and my Average in MB4819 should be =AVERAGE(MB1015:MB4811). Similarly for LY, LZ, etc. This is to fill in those early entries with an average of all those where I did not have data, but only calculating the average from when I do have data (I hope that makes sense). Once I'd corrected that (and why Error Checking didn't pick it up, I do not know), my SumProduct problem seems to have disappeared. Hurrah. Nad many thanks toyou (and others) who took the time and trouble to help. I really appreciate this.

As to the calculation chain from Creatinine, to Collations2, to the NormData SumProducts in cols B - F, I don't think there's any circularity there. I've been using that loop for some time and haven't had any problems from that part of my work.

As to my 'not work' etc, I'm afraid I do not recall having done this entry - I have looked for it, but so far have failed to find it. So, alas, I cannot offer any explanation for it. If perhaps you were referring to "BAD" entries, these are those that are inconsistent for Cardio and Kidney - that is, good for one, but bad for the other and so best not take/do that thing.