Absolutely Stumped!

[Brett]

Does anyone know what it means when a cell containing a formula, displays a
result that is wrong, but the correct value shows in the "Function
Arguements" dialog box near the bottom where it says "Formula result ="?

[Peo Sjoblom]

Enter it with ctrl + shift & enter

--

Regards,

Peo Sjoblom


"Brett" <Brett@discussions.microsoft.com> wrote in message
news:1640E1F7-3D4A-4F9C-955A-5DB36C2BF3E8@microsoft.com...
> Does anyone know what it means when a cell containing a formula, displays
a
> result that is wrong, but the correct value shows in the "Function
> Arguements" dialog box near the bottom where it says "Formula result ="?

[Ken Wright]

Anything happen if you hit F9?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Brett" <Brett@discussions.microsoft.com> wrote in message
news:1640E1F7-3D4A-4F9C-955A-5DB36C2BF3E8@microsoft.com...
> Does anyone know what it means when a cell containing a formula, displays
a
> result that is wrong, but the correct value shows in the "Function
> Arguements" dialog box near the bottom where it says "Formula result ="?

[JE McGimpsey]

Depends on the formula. Why don't you tell us what it is...? And you
probably ought to let us know what the "correct" result and the "wrong"
result are, as well.

One cause could be that the formula needs to be array-entered (with
CTRL-SHIFT-ENTER or CMD-RETURN). There are others.




In article <1640E1F7-3D4A-4F9C-955A-5DB36C2BF3E8@microsoft.com>,
"Brett" <Brett@discussions.microsoft.com> wrote:

> Does anyone know what it means when a cell containing a formula, displays a
> result that is wrong, but the correct value shows in the "Function
> Arguements" dialog box near the bottom where it says "Formula result ="?

[Brett]

The formula is a very complicated "mega formula" involving many levels of
named formulas containing array formulas. The cell formula I was refering to
was array-entered so that's not the problem. The formula was evaluating
properly until I added a 5th level to the nested IF statements. The formula
has been copied down a column and the first 3 rows evaluate correctly, but
from the 4th row on, the value displayed is the same as row 3, when it should
be a different value (the one showing in "Funtion Arguements" dialog box
(see original posting)). Even more puzzling, is that the 5th level in the
nested IF statement shouldn't really be causing a problem, since the formula
evaluates to TRUE at the 3rd nested IF statement and the 5th level would not
even be evaluated unless the 3rd level evaluated to FALSE.
Could it be that my computer doesn't have enough RAM (512MB)?

"JE McGimpsey" wrote:

> Depends on the formula. Why don't you tell us what it is...? And you
> probably ought to let us know what the "correct" result and the "wrong"
> result are, as well.
>
> One cause could be that the formula needs to be array-entered (with
> CTRL-SHIFT-ENTER or CMD-RETURN). There are others.
>
>
>
>
> In article <1640E1F7-3D4A-4F9C-955A-5DB36C2BF3E8@microsoft.com>,
> "Brett" <Brett@discussions.microsoft.com> wrote:
>
> > Does anyone know what it means when a cell containing a formula, displays a
> > result that is wrong, but the correct value shows in the "Function
> > Arguements" dialog box near the bottom where it says "Formula result ="?
>

[Ken Wright]

I'm with JE in that without seeing it it's very hard to do much about it,
albeit given you have multiple named formulas you would have to list those
too.

That having been said, if it's really evaluating correctly initially and
then failing as you copy down, then that is usually a sign of having a range
in the formula that is relative when it is supposed to be absolute. Any
chance that could be the case?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>

[CyberTaz]

Ditto Ken & JE about shooting in the dark, but it sounds like Ken's on the
right track about the absolute reference. There could also be something
related to the order of operations if there was some editing done recently
(as in "The formula was evaluating properly until I added a 5th level to the
nested IF statements.") |:>)

"Ken Wright" wrote:

> I'm with JE in that without seeing it it's very hard to do much about it,
> albeit given you have multiple named formulas you would have to list those
> too.
>
> That having been said, if it's really evaluating correctly initially and
> then failing as you copy down, then that is usually a sign of having a range
> in the formula that is relative when it is supposed to be absolute. Any
> chance that could be the case?
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
> <snip>
>
>
>

[Biff]

Can one really build a "mega formula" with the Insert
Function dialog?

Biff

>-----Original Message-----
>Ditto Ken & JE about shooting in the dark, but it sounds
like Ken's on the
>right track about the absolute reference. There could
also be something
>related to the order of operations if there was some
editing done recently
>(as in "The formula was evaluating properly until I added
a 5th level to the
>nested IF statements.") |:>)
>
>"Ken Wright" wrote:
>
>> I'm with JE in that without seeing it it's very hard to
do much about it,
>> albeit given you have multiple named formulas you would
have to list those
>> too.
>>
>> That having been said, if it's really evaluating
correctly initially and
>> then failing as you copy down, then that is usually a
sign of having a range
>> in the formula that is relative when it is supposed to
be absolute. Any
>> chance that could be the case?
>>
>> --
>> Regards
>> Ken....................... Microsoft MVP -
Excel
>> Sys Spec - Win XP Pro / XL 97/00/02/03
>>
>> --------------------------------------------------------
--------------------
>> It's easier to beg forgiveness than
ask permission :-)
>> --------------------------------------------------------
--------------------
>> <snip>
>>
>>
>>
>.
>

[Brett]

OK. After further troubleshooting I have isolated the source of the problem.
The problem was not coming from the formulas in the cells that were
displaying the incorrect values. I thought that adding a 5th level to the
nested IF statements was the start of the problem, but that wasn't it. I had
also made a change to the cell formulas in an adjacent column which were
being referenced by the cells having the display problem.

Here is the formula being referenced that is causing the problem:

IF(SUM(IF((Expiry_Dates_Array-TODAY()>=0)*(Expiry_Dates_Array-TODAY()<=2),1,0))-ROW()+Last_Non_Expiry_Message_Row+1>0,Expiry_Message,"")

More specifically, the volatile function TODAY(), in the above formula, is
responsible for the error. I don't understand why this is, but if I replace
TODAY() with the date code, the problem disappears. Too bad I can't use this
function! I'll have to think up some other way. Any suggestions?

"Ken Wright" wrote:

> I'm with JE in that without seeing it it's very hard to do much about it,
> albeit given you have multiple named formulas you would have to list those
> too.
>
> That having been said, if it's really evaluating correctly initially and
> then failing as you copy down, then that is usually a sign of having a range
> in the formula that is relative when it is supposed to be absolute. Any
> chance that could be the case?
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
> <snip>
>
>
>

[Ken Wright]

> Here is the formula being referenced that is causing the problem:
>
>
IF(SUM(IF((Expiry_Dates_Array-TODAY()>=0)*(Expiry_Dates_Array-TODAY()<=2),1,
0))-ROW()+Last_Non_Expiry_Message_Row+1>0,Expiry_Message,"")

The TODAY() function affects only the first part of your formula, ie the

=SUM(IF((Expiry_Dates_Array-TODAY()>=0)*(Expiry_Dates_Array-TODAY()<=2),1,0)
)

That works fine for me assuming that is meant to count records within 2 days
of expiry, so can't see why that would kill it. When you say date code, I'm
assuming you mean something like 38409. Obviously though, it's hard to
really tell without all the other linking formulas etc.

Another way of doing that with less function calls would be as follows:-

=SUMPRODUCT(--(Expiry_Dates_Array-TODAY()>=0),--(Expiry_Dates_Array-TODAY()<
=2))

There is no need to array to array enter the above section using SUMPRODUCT
as is, but if your formula is part of a bigger one then that may mean the
whole thing still has to be array entered.

You might also want to consider putting TODAY() into a cell, naming it and
then referencing that cell. I think it will only have to evaluate TODAY()
once that way, though not 100% sure wrt a volatile function to be honest. I
expect somebody will pick me up if I'm wrong there :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------
<snip>