Sumproduct: Formula is finicky

M

Mike C

Hi there.
I have a cell that contains one long sumproduct formula. It is basically
combining 3 results into 1 cell with a "/" separating them. This formula is
repeated 448 times For the past week the formulas have worked great, but
today they only return "#N/A". My process for updating the data hasn't
changed nor has the data.

Now if I retype the formula and press enter, I get a correct result.

Why in the world is Excel forcing me to retype the exact same formula in the
exact same cell without any changes and then giving me a result? This is
immensly frustrating.

Any ideas?
 
N

Niek Otten

<Any ideas?>

Not really, except of course I suspect you "do" have changed data or
formulas <g>.

I'd try to rebuild the dependency tree with CTRL+ALT+SHIFT+F9

Another way of "re-entering" your formulas is to find all "=" and replace by
"="
 
M

Mike C

I appreciate the help, although both suggestions were tried and the same
result occured.
I noticed that when in the original file, if I introduce the new data in
smaller ranges, it doesn't blast my tables with "#N/A". You may have a point
about the data being modified.

--
Thanks,
Mike


Niek Otten said:
<Any ideas?>

Not really, except of course I suspect you "do" have changed data or
formulas <g>.

I'd try to rebuild the dependency tree with CTRL+ALT+SHIFT+F9

Another way of "re-entering" your formulas is to find all "=" and replace by
"="
Click to expand...
 
B

Bob Phillips

#N/A is usually returned when the range sizes are not the same.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
M

Mike C

I got it.
This is really dumb...but it turned out that one of my vlookup values
returned an #N/A, which strangely enough, rendered the whole set of tables as
#N/A.
Go figure...
--
Thanks,
Mike


Bob Phillips said:
#N/A is usually returned when the range sizes are not the same.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
Top