Sumproduct: Formula is finicky

[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?

--
Thanks,
Mike

[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
"="

--
Kind regards,

Niek Otten

"Mike C" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> --
> Thanks,
> Mike

[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" wrote:

> <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
> "="
>
> --
> Kind regards,
>
> Niek Otten
>
> "Mike C" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> > --
> > Thanks,
> > Mike
>
>
>

[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)

"Mike C" <[email protected]> wrote in message
news:[email protected]...
> 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?
>
> --
> Thanks,
> Mike

[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" wrote:

> #N/A is usually returned when the range sizes are not the same.
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Mike C" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
> > --
> > Thanks,
> > Mike
>
>
>