Absolutely Stumped!

Enter it with ctrl + shift & enter

--

Regards,

Peo Sjoblom


"Brett" <[email protected]> wrote in message
news:[email protected]...
> 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 ="?

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

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 <[email protected]>,
"Brett" <[email protected]> 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 ="?

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

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 <[email protected]>,
> "Brett" <[email protected]> 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 ="?
>

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>

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

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>
>>
>>
>>
>.
>

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

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

Ken, thanks for the tip about SUMPRODUCT! I've never used this function
before, but it looks very useful.
I was also thinking the problem may be solved by referencing TODAY() from a
single cell instead of calling the function in every cell of the column where
it's needed. Unfortunately, that didn't work either.
One solution, if it's doable, is something similar to the above, but instead
of entering the function TODAY() into the cell, "Paste Value" instead. I
would have to write a macro that would do this every time a recalculation was
made though, or else do it manually once a day.
Although, I have created a few macros, my knowledge of VB is very limited.
However, I do remember reading something on the web about macros that can
detect certain events, like recalculations, and then run a procedure in
response. Having a macro to do this would make things a lot easier, but I can
see this task consuming a lot of my time.
Wish there were a simpler solution!


"Ken Wright" wrote:

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

I'm struggling to see why it won't work to be honest, and am wondering if it
means that there is perhaps another error in your formula somewhere. If you
try the section of the formula that I outlined on it's own, you should see
that it works in isolation, so there is little reason to believe it won't
work as part of another formula. The SUMPRODUCT formula also works fine in
isolation, so same again.

Have you tried breaking the formula down and testing each of the elements
individually, or perhaps using Tools / Formula Auditing / Evaluate to see
which parts of the formula don't evaluate correctly?

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

Sorry, should also have given you a link for an explanation of SUMPRODUCT:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

Take a look for yourself.
I've created a very simplified example of the original worksheet problem
I've been having. The example given below isn't a perfect representation of
what I've been trying to accomplish, so I'm not looking for a more elegant
way of writing it, but it does demonstrate the same problem that arises when
using the TODAY() function.
Basically, the example attempts to return the smallest integer >0 which does
not exist previously in the column, or has a date which is expired.

Instructions: Highlight and copy the following, then on a blank worksheet,
select cell A1 and paste the example.

=TODAY()

=TODAY()-3 =IF(A3-A$1<0,"Expired","") 1
=TODAY() =IF(A4-A$1<0,"Expired","") 2
=TODAY()-2 =IF(A5-A$1<0,"Expired","") 4
=TODAY()+1 =IF(A6-A$1<0,"Expired","") 5
=TODAY()-1 =IF(A7-A$1<0,"Expired","") 7
=IF(ISNA(MATCH(1,IF(B$3:B7<>"Expired",C$3:C7),0)),1,MIN(IF(ISNA(MATCH(C$3:C7+1,IF(B$3:B7<>"Expired",C$3:C7),0)),C$3:C7+1)))

Now select cell C8 and array-enter the formula. Then copy the formula down
C9:C13.
Next copy column A and "Paste Values" to column E. Then copy columns B:C and
"Paste Formulas" to columns F:G. Done.

Now you have 2 examples side-by-side where the only difference is that the
one on the left uses the TODAY() function and the example on the right uses
the date codes instead.
Notice how G8:G13 returns the correct values while C8:C13 does not.

Any thoughts appreciated. Thanks.


"Ken Wright" wrote:

> I'm struggling to see why it won't work to be honest, and am wondering if it
> means that there is perhaps another error in your formula somewhere. If you
> try the section of the formula that I outlined on it's own, you should see
> that it works in isolation, so there is little reason to believe it won't
> work as part of another formula. The SUMPRODUCT formula also works fine in
> isolation, so same again.
>
> Have you tried breaking the formula down and testing each of the elements
> individually, or perhaps using Tools / Formula Auditing / Evaluate to see
> which parts of the formula don't evaluate correctly?
>
> --
> 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>
>
>
>

Take a look for yourself.
I've created a very simplified example of the original worksheet problem
I've been having. The example given below isn't a perfect representation of
what I've been trying to accomplish, so I'm not looking for a more elegant
way of writing it, but it does demonstrate the same problem that arises when
using the TODAY() function.
Basically, the example attempts to return the smallest integer >0 which does
not exist previously in the column, or has a date which is expired.

Instructions: Highlight and copy the following, then on a blank worksheet,
select cell A1 and paste the example.

=TODAY()

=TODAY()-3 =IF(A3-A$1<0,"Expired","") 1
=TODAY() =IF(A4-A$1<0,"Expired","") 2
=TODAY()-2 =IF(A5-A$1<0,"Expired","") 4
=TODAY()+1 =IF(A6-A$1<0,"Expired","") 5
=TODAY()-1 =IF(A7-A$1<0,"Expired","") 7
=IF(ISNA(MATCH(1,IF(B$3:B7<>"Expired",C$3:C7),0)),1,MIN(IF(ISNA(MATCH(C$3:C7+1,IF(B$3:B7<>"Expired",C$3:C7),0)),C$3:C7+1)))

Now select cell C8 and array-enter the formula. Then copy the formula down
C9:C13.
Next copy column A and "Paste Values" to column E. Then copy columns B:C and
"Paste Formulas" to columns F:G. Done.

Now you have 2 examples side-by-side where the only difference is that the
one on the left uses the TODAY() function and the example on the right uses
the date codes instead.
Notice how G8:G13 returns the correct values while C8:C13 does not.

Any thoughts appreciated. Thanks.


"Ken Wright" wrote:

> I'm struggling to see why it won't work to be honest, and am wondering if it
> means that there is perhaps another error in your formula somewhere. If you
> try the section of the formula that I outlined on it's own, you should see
> that it works in isolation, so there is little reason to believe it won't
> work as part of another formula. The SUMPRODUCT formula also works fine in
> isolation, so same again.
>
> Have you tried breaking the formula down and testing each of the elements
> individually, or perhaps using Tools / Formula Auditing / Evaluate to see
> which parts of the formula don't evaluate correctly?
>
> --
> 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>
>
>
>

Sorry, I should have included in the instructions to "Paste Special / Text"
the example to cell A1.

"Brett" wrote:

> Take a look for yourself.
> I've created a very simplified example of the original worksheet problem
> I've been having. The example given below isn't a perfect representation of
> what I've been trying to accomplish, so I'm not looking for a more elegant
> way of writing it, but it does demonstrate the same problem that arises when
> using the TODAY() function.
> Basically, the example attempts to return the smallest integer >0 which does
> not exist previously in the column, or has a date which is expired.
>
> Instructions: Highlight and copy the following, then on a blank worksheet,
> select cell A1 and paste the example.
>
> =TODAY()
>
> =TODAY()-3 =IF(A3-A$1<0,"Expired","") 1
> =TODAY() =IF(A4-A$1<0,"Expired","") 2
> =TODAY()-2 =IF(A5-A$1<0,"Expired","") 4
> =TODAY()+1 =IF(A6-A$1<0,"Expired","") 5
> =TODAY()-1 =IF(A7-A$1<0,"Expired","") 7
> =IF(ISNA(MATCH(1,IF(B$3:B7<>"Expired",C$3:C7),0)),1,MIN(IF(ISNA(MATCH(C$3:C7+1,IF(B$3:B7<>"Expired",C$3:C7),0)),C$3:C7+1)))
>
> Now select cell C8 and array-enter the formula. Then copy the formula down
> C9:C13.
> Next copy column A and "Paste Values" to column E. Then copy columns B:C and
> "Paste Formulas" to columns F:G. Done.
>
> Now you have 2 examples side-by-side where the only difference is that the
> one on the left uses the TODAY() function and the example on the right uses
> the date codes instead.
> Notice how G8:G13 returns the correct values while C8:C13 does not.
>
> Any thoughts appreciated. Thanks.
>
>
> "Ken Wright" wrote:
>
> > I'm struggling to see why it won't work to be honest, and am wondering if it
> > means that there is perhaps another error in your formula somewhere. If you
> > try the section of the formula that I outlined on it's own, you should see
> > that it works in isolation, so there is little reason to believe it won't
> > work as part of another formula. The SUMPRODUCT formula also works fine in
> > isolation, so same again.
> >
> > Have you tried breaking the formula down and testing each of the elements
> > individually, or perhaps using Tools / Formula Auditing / Evaluate to see
> > which parts of the formula don't evaluate correctly?
> >
> > --
> > 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>
> >
> >
> >

I'm assuming then that you get in cells:-

C8 1 G8 1
C9 2 G9 3
C10 2 G10 4
C11 2 G11 6
C12 2 G12 7
C13 2 G13 8

and if you copy A3:A7 into E3:E7 then you get

C8 1 G8 1
C9 2 G9 2
C10 2 G10 2
C11 2 G11 2
C12 2 G12 2
C13 2 G13 2

so therefore seeing as the only difference is the TODAY() function it must
be that yes?

Assuming that to be the case, have you then tried reversing that logic to
see if it holds true the other way, because if you were correct, then
copying E3:E7 to A3:A7 I assume you would expect to return:-

C8 1 G8 1
C9 3 G9 3
C10 4 G10 4
C11 6 G11 6
C12 7 G12 7
C13 8 G13 8

Try it though :-)

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

I'm struggling, sorry :-( Evaluate Formula tells me that C9 should be 3
yet it returns 2

--
Regards
Ken....................... <snip>

Grrr - hadn't realised that the formulas in Col B were referencing a
TODAY() - apologies.

--
Regards
Ken.......................

<snip>