Absolutely Stumped!

B

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

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

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)?
 
K

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

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.") |:>)
 
B

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

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 :)
 
B

Brett

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

Ken Wright

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

Brett

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

Brett

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

Brett

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

Ken Wright

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 :)
 
K

Ken Wright

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

Ken Wright

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