"Fake" #N/A Calculation Behavior When Pasting Formula Down

SigmaCrisis

New Member
Joined
Jun 7, 2017
Messages
2
Hello fellow Excel aficionados!

I have a question regarding some odd behavior that one of my models is exhibiting whenever I'm making tweaks/updates to its structure. To be clear, this is not an issue with my formulas, but rather behavior when I make changes/refresh things in the formula structure -- all of my formulas are WORKING except for this odd bug which I have to work around. My hope is that maybe someone can shed some light as to why this is happening, because I'm left scratching my head, with only a vague suspicion that the answer is involved with how excel tries to compute which cells need to be calculated/recalculated at any given time.

I have the following formula that does its job splendidly:
Code:
=SUMPRODUCT(('Resource Planning'!$F$37:$DG$37=TRIM(F$6))*('Resource Planning'!$D$39:$D$340=[COLOR=#FF0000]$A11[/COLOR])*('Resource Planning'!$B$39:$B340=$A$10)*('Resource Planning'!$F$39:$DG$340))/F$7

I can copy -> paste special formula across columns, no problem. However, whenever I paste DOWN rows, the formula evaluates to #N/A. Now before you go through the effort of trying to break down the formula, please note that I've highlighted (above in red) the only cell reference that changes when you paste this formula down a row.

As expected when I paste down, the cell reference $A11 becomes $A12. That's the only change in the formula.

Observed behavior: the formula evaluates to #N/A, even when the contents of $A11 and $A12 are identical. It should actually evaluate to a number, based on the sumproduct aggregation I've set up.

Workarounds: I actually have two workaround methodologies that I've stumbled upon.

Method 1: Copy the formula text from the source formula cell, edit the target cell one row below (F2), paste the formula text, then manually change $A11 to $A12.

I don't really understand WHY this works. If I prepare a version of the formula with $A12 in a .txt file and use that to copy/paste directly into the target cell, it still evaluates to #N/A. I HAVE to paste the $A11 version and then change the reference. I can change the reference while editing the cell before pressing enter and this methodology still works.

This leads me to believe that the issue lies in how excel is determining when to recalculate/evaluate cells, and I can only 'trick' it into calculating the reference change to $A12 when I start at a known reference ($A11). Note that this behavior occurs whether I have calculations set to automatic or manual.

Method 2: Occasionally, I can get the formula pasted down that bugs to #N/A to recalculate properly if I go to the source data sheet 'Resource Pool' and delete rows BELOW the actual data. I'm talking rows 341 and below (nothing references below row 340).

The rows are empty and generally unformatted beyond the default formatting applied. I've messed around with the "used range" attribute of the sheet and am convinced it has no impact on the issue, regardless of whether or not I reset it using VBA. This further reinforces my thought that it has SOMETHING to do with tricking excel into properly re-evaluating the new formula.

----

Any thoughts/ideas on how to further pinpoint and possibly fix this issue? Bonus question: why does this behavior occur only when pasting down rows and not across columns?


A few notes that might be relevant:
-I've confirmed there's nothing weird going on with names, cell formatting, conditional formatting, data sources, etc.
-Although the system I'm using is a bit dated, I'm not running out of ram or hanging on processing.
-I don't have any macros/permanent VBA code in this file, so nothing complex should be occurring on that front.

Thanks in advance!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
You are missing a $ here:

=SUMPRODUCT(('Resource Planning'!$F$37:$DG$37=TRIM(F$6))*('Resource Planning'!$D$39:$D$340=$A11)*('Resource Planning'!$B$39:$B340=$A$10)*('Resource Planning'!$F$39:$DG$340))/F$7

Assuming you only want $A11 to change when you copy down, then that should be $B$340 no?

And welcome to the forum.
 
Last edited:

SigmaCrisis

New Member
Joined
Jun 7, 2017
Messages
2
Well I feel incredibly foolish. This just so happened to make its way into ~several dozen various different sumproduct formulae in this file and I had somehow convinced myself it was systemic. This just so happened to be the basic form that was the 'bad seed' so to speak.

Thank you for pointing out the obvious, and for giving my hubris a nice trim. XD
 

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
Well I feel incredibly foolish. This just so happened to make its way into ~several dozen various different sumproduct formulae in this file and I had somehow convinced myself it was systemic. This just so happened to be the basic form that was the 'bad seed' so to speak.

Thank you for pointing out the obvious, and for giving my hubris a nice trim. XD

You're quite welcome, and in good company. ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top