Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Absolutely Stumped!
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 ="? |
#2
|
|||
|
|||
Enter it with ctrl + shift & enter
-- Regards, Peo Sjoblom "Brett" wrote in message ... 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 ="? |
#3
|
|||
|
|||
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" wrote in message ... 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 ="? |
#4
|
|||
|
|||
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 , "Brett" 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 ="? |
#5
|
|||
|
|||
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 , "Brett" 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 ="? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 . |
#9
|
|||
|
|||
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+10,Expiry_Messa ge,"") 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 |
#10
|
|||
|
|||
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+10,Expiry_Messa ge,"") 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 |
#11
|
|||
|
|||
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+10,Expiry_Messa ge,"") 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 |
#12
|
|||
|
|||
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 |
#13
|
|||
|
|||
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 |
#14
|
|||
|
|||
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 |
#15
|
|||
|
|||
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 |
#16
|
|||
|
|||
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 |
#17
|
|||
|
|||
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 |
#18
|
|||
|
|||
I'm struggling, sorry :-( Evaluate Formula tells me that C9 should be 3
yet it returns 2 -- Regards Ken....................... <snip |
#19
|
|||
|
|||
Grrr - hadn't realised that the formulas in Col B were referencing a
TODAY() - apologies. -- Regards Ken....................... <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple But Stumped | Excel Discussion (Misc queries) | |||
List box has me stumped. | Excel Discussion (Misc queries) | |||
Stumped by IF | Excel Worksheet Functions | |||
Stumped! Collecting values into one cell. | Excel Worksheet Functions | |||
Stumped! Collecting values into one cell. | Excel Worksheet Functions |