+ Reply to Thread
Results 1 to 32 of 32

Strange Value Error

  1. #1
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Unhappy Strange Value Error

    Dear All,

    I have 2 excel files..
    I have no problem in 1 of these two excel files. But I get Value error in other Excel file. Both excel files are very similar in structure to each other.

    The excel file that I got the Value error works fine on other computers. I could not find where the problem originated. Please can you help?

    Works smoothly on all computers. Including my computer.
    EXCEL_18PUPSgqZh.png

    It gives Value error on my computer. It runs smoothly on other computers.
    EXCEL_7aKvLskpC1.png


    My Office ver: O365
    Please share your recommendations with me.
    Last edited by flexelem; 02-10-2020 at 06:06 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    Welcome to the forum.

    S5 in the problem file must be presenting as text. Change the S5 reference to VALUE(S5) and see if it fixes it.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    Hi AliGW,

    Thank you for your kindly welcome.
    I made the change you mentioned and added it as Gif. Coul you please examine?
    "Please be aware, this excel file runs smoothly on another computer."

    toW6lQKXZE.gif

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    No, you have not made the change I suggested.

    I said change this:

    S5

    to this:

    VALUE(S5)

    All you did was add the brackets, not the function. You need to do this to both instances of S5.

  5. #5
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    Unfortunately, your advice is still not clear to me.
    You say I need to make this change over S5. In S5, the situation is as follows;

    EXCEL_twLnmHtGjW.png

    EXCEL_TEhK4732kR.png

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    No, I did not say that.

    I said that you need to change your formula to this:

    =IF(AND(VALUE(S5)<1,5;VALUE(S5)>-1,5);0;1)

  7. #7
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    Hello,

    I made the change you specified, but the result did not change.

    Attachment 661887

  8. #8
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    @flexelem, I completely understand your frustration.

    The fastest way to get help is to follow the instructions in the bright yellow banner at top of this webpage and provide an Excel file -- not an image -- that demonstrates the problem.

    (While I was writing this, I see that you tried to do just that. But unfortunately, I cannot access the link for some reason. I thought someone explained the common mistake that causes that. But unfortunately, I cannot find that information right now.)

    I believe you are saying that the following formula returns a #VALUE error:

    =IF(AND(S5<1,5; S5>-1,5); 0; 1)

    I believe AliGW is suggesting that you change the formula to:

    =IF(AND(VALUE(S5)<1,5; VALUE(S5)>-1,5); 0; 1)

    If both of my assumptions are correct, I don't why AliGW know why she suggested it, in the first place.

    If S5 is a text value (note: the format of the cell does not matter), S5<1 returns FALSE and S5>1,5 returns TRUE because in Excel, all text is treated as greater than all numeric values.

    Consequently, the AND function always returns FALSE, and the IF function always returns 1.

    It would not return a #VALUE error just because S5 is a text value.

    Also, that formula would not return a #VALUE error if the syntax is incorrect for your region or the "system separators" option for your Excel application. Instead, Excel would complain about the syntax, and it would not allow you to enter the formula.

    The only way that I can imagine that formula would return a #VALUE error is if S5 displays a #VALUE error.

    Unfortunately, I have not seen enough context in the uploaded images to determine that.
    Last edited by joeu2004; 02-10-2020 at 03:21 AM. Reason: minor typos in formulas

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Strange Value Error

    flexelem welcome to the forum

    Just to back-up what has been suggested, it is always better if you upload a sample workbook, not a pic - we cant work with pics, and honestly, no-one wants to retype your data for you - plus we cannot tell what your actual cell contents are, now what formatting you may or may not have.

    The yellow banner above explains how to upload your file - if you still have a problem doing that, shout and we can explain further
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  10. #10
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    Quote Originally Posted by joeu2004 View Post
    The only way that I can imagine that that formula would return a #VALUE error is if S5 displays a #VALUE error.
    Unfortunately, I have not seen enough context in the uploaded images to determine that.
    Oh, there it is in one of your later png files.

    It does indeed appear that S5 is displaying a #VALUE error. The formula in S5 is: =S181 - A2!S167 .

    But again, we have insufficient context to know why that might result in a #VALUE error.

    Presumably, one of S181 or A2!167 is a text value that Excel cannot interpret as numeric.

    On the other hand, perhaps one of those cell contains a #VALUE error. And on and on and on.

    We __must__ be able to download the offending Excel file, or an example Excel that demonstrates the same error with the same formulas and relevant values.
    Last edited by joeu2004; 02-10-2020 at 03:22 AM.

  11. #11
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    @FDibbins, I cannot access flexelem's attachment 661887. I get the error ``Invalid Attachment specified. If you followed a valid link, please notify the administrator``.

    Sigh, you or someone else explained why that happens. I forgot why. I don't think it's my problem, is it? I have cleared all browser history.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    I both of my assumptions are correct, I don't why AliGW know why she suggested it, in the first place.
    Thanks for this.

    The OP has said more than once that the offending file works OK on other machines - so I'm not sure that seeing the workbook, in this case, will get us any further forward.

  13. #13
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    @AliGW, can you help flexelem resolve the problem with attachment 661887? Or help me, if it's my problem?!

    Seeing the actual workbook is always better than playing "20 questions", especially when language issues might be a factor.

    I requested an example Excel file that __demonstrates__ the problem. Obviously, flexelem has one; presumably, it is the source of the pngs.

    As for claims that the "same" file works here, but not there, I can only say that "Greg House" rules apply. (I'm referring to an American TV show.)

    Regardless, I am very good at inferring "possible mistakes", even if the example Excel does not demonstrate the problem.

    PS.... Sorry about the botched "I don't know why" comment. Bad editing. It should have read: ``I don't know why AliGW suggested it, in the first place``.

    (And I botched it again!)
    Last edited by joeu2004; 02-10-2020 at 03:31 AM.

  14. #14
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Strange Value Error

    Are your regional settings the same on all your machines?

  15. #15
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    Quote Originally Posted by joeu2004 View Post
    @FDibbins, I cannot access flexelem's attachment 661887. [....] Sigh, you or someone else explained why that happens. I forgot why.
    Aha! I found the explanation: ``The Forum Server reports it as an invalid link. I'd say it is the OP's issue not yours``, according to Logit in response to my question at
    https://www.excelforum.com/suggestio...e-posters.html .

    But Logit never explained what that "issue" might be.

    @FDibbins or @AliGW, I would appreciate an explanation of what the mistake is (or might be), for my edification. I have never encountered the problem myself, following the simple instructions in the banner at the top of the webpage.

  16. #16
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    Thank you all for your great support and feedback.
    You can find the file in the attachment.
    Attached Files Attached Files

  17. #17
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Strange Value Error

    joeu2004 the attachment is an image file, not an excel file - maybe that would explain why you cant open it?

  18. #18
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    Quote Originally Posted by joeu2004 View Post
    Also, that formula would not return a #VALUE error if the syntax is incorrect for your region or the "system separators" option for your Excel application. Instead, Excel would complain about the syntax, and it would not allow you to enter the formula.
    Although that comment is correct with respect to the offending formula itself....

    Quote Originally Posted by joeu2004 View Post
    It does indeed appear that S5 is displaying a #VALUE error. The formula in S5 is: =S181 - A2!S167 . [....] Presumably, one of S181 or A2!167 is a text value that Excel cannot interpret as numeric.
    Regional differences in the data might be a very good explanation for Excel's inability to interpret as a numeric value.

    @flexelem.... For example, you write numbers like 1,5 to mean 1 + 5/10. In other words, your decimal point is a comma.

    I would write the same number as 1.5 . In other words, my decimal point is a period.

    Usually, that is not a problem when workbooks are shared between regions because when I open your Excel workbook with the numeric value 1,5 , Excel automatically presents it as 1.5 .

    However, if your workbook contain text data in the form 1,5 , that is not converted.

    In your region, text data in the form 1,5 can be used in arithmetic expressions.

    But in my region, text data in that form cannot. It would indeed cause a #VALUE error.

    (And vice versa, if you are the one providing the "numeric" text data.)

    The remedy is: convert all "numeric" text data to bona fide numeric data. The Text To Column might accomplish that.

    Of course, that is a wild guess, and it could be a misdirection.

    Again, it is better if we can see an offending Excel file, and stop playing guessing games.

  19. #19
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    It should have read: ``I don't know why AliGW suggested it, in the first place``.
    Yes, I know. Why do you think I suggested it? Isn't the whole point of suggestions to try to help the OP solve a puzzle? Of course, if your superior knowledge makes my suggestion look stupid, then you are entirely justified in pouring scorn on it.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    @Flexeem - I have looked at your workbook. The formula makes no sense to me: why are you testing whether S5 is between -1.5 and 1.5? Even if it really were a date (which it isn't - it's just text, because H21 on the INPUT tab has been entered as text instead of a proper date), this test would always evaluate to 0.

    The reason you are getting the #VALUE error is because it, and the cell feeding it, both contain text.

  21. #21
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    Quote Originally Posted by flexelem View Post
    Thank you all for your great support and feedback. You can find the file in the attachment.
    Finally, something to work with. But for the future, please note that in this forum, it is not necessary to "zip" files, AFAIK. It is much easier if you attach the Excel file directly.

    Your Excel file is riddled with #VALUE errors. Some, like S5, are "invisible" because of the font color (same as the background).

    I tried to drill down on the many #VALUE errors to find one concrete example. But there are just too many SUBTOTAL and SUM formulas that reference ranges with #VALUE errors.

    So you must do the legwork. Keep drilling down on formulas with #VALUE errors until you find at least one cell reference that returns a #VALUE error, and the referenced cell contains a constant.

    As I mentioned, I believe you will find that constant is text, and it is a form that cannot be interpreted as numeric in some regions. Use ISTEXT(cellref) to confirm the text value. The cell format does not matter. And a visual inspection can be misleading.

    If you are totally stuck, please share your "drill down" with us. That is, point us to the "last" cell in the chain of references that displays a #VALUE error.

    For example, S5 displays #VALUE because S181 and A2!S167 display #VALUE errors. S181 has the formula =SUM(S7,S89), and S7 and S89 display #VALUE errors. S7 has the formula =SUBTOTAL(9,S8:S87). Presumably, one or more cells in the range S8:S87 display #VALUE errors. I did not determine which one(s).

    Continue in that manner until you find the last cell that displays #VALUE because it references a cell that contains a constant.

  22. #22
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    [.... deleted by me; offensive ....]
    Last edited by joeu2004; 02-10-2020 at 04:35 AM.

  23. #23
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    This excel file works fine on all my colleagues. I just have a problem. So I don't think this is a formula error.


    Quote Originally Posted by joeu2004 View Post
    Finally, something to work with. ...
    Last edited by AliGW; 02-10-2020 at 04:33 AM. Reason: Please don't quote unnecessarily!

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    It is an error in that workbook - please see post #20.

  25. #25
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    It may not be related to the regional setting.
    You ask why ? Because I have no problems with a similar Excel file that I have attached this post.
    Plase check...

    Actually that's why it's a very strange problem.
    Attached Files Attached Files

  26. #26
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Strange Value Error

    Quote Originally Posted by flexelem View Post
    This excel file works fine on all my colleagues. I just have a problem. So I don't think this is a formula error.
    You quote my response, but __I__ never said that you have formula errors. AliGW wrote that.

    AliGW seems to think that the root cause (or one of them) with S5 returning #VALUE is the data in INPUT!H21.

    Frankly, I don't know that analysis to be correct or incorrect. But assuming it is correct....

    When I open your Excel file, INPUT!H21 appears to be 23.01.2020 . The cell is formatted as Text. And more to the point, =ISTEXT(H21) returns TRUE.

    In your region, =--H21 might return a numeric value because Excel might interpret that text as a valid date.

    But in my region, =--H21 returns #VALUE becaue Excel cannot interpret that as a valid date.

    Consequently, if you use INPUT!H21 in a numeric expression, it might work for you, but not for me.

    Hopefully, that helps you understand the regional sensitivity, if not with INPUT!H21 per se, then perhaps some other cells with "numeric" text.

    The remedy is: avoid using text for data that you intend to be treated as numeric.
    Last edited by joeu2004; 02-10-2020 at 05:16 AM.

  27. #27
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    AliGW seems to think that the root cause (or one of them) with S5 returning #VALUE is the data in INPUT!H21.
    I say this because the same error is present when I open the file and that cell contains text. If I change it to a proper date, the error goes away.

    I have not had time (and don't have time now) to look at the second sample file. Over to you, maestro.

  28. #28
    Registered User
    Join Date
    02-09-2020
    Location
    Istanbul
    MS-Off Ver
    O365
    Posts
    15

    Re: Strange Value Error

    Hello Everyone,


    I have good news.
    Indeed, this issue is all about the date format in Windows settings.
    I changed . instead of / in the date format and my problem was fixed.

    Thank you very much for all your help. You took the time and tried to support.
    Last edited by flexelem; 02-10-2020 at 06:26 AM.

  29. #29
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    Good news. Thanks for letting us know.

  30. #30
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Strange Value Error

    As far as I can tell, all the problems are caused by the fact that all the dates on the input sheet have been entered as text for no apparent reason. If they were entered as actual dates, then the rest of the workbook should work regardless of regional settings.
    Rory

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Strange Value Error

    Yep - that was my point in post #20.

  32. #32
    Registered User
    Join Date
    12-13-2022
    Location
    New York
    MS-Off Ver
    10
    Posts
    10

    Re: Strange Value Error

    Make your date column wider. If your date is aligned to the right, then it's a date. But if it's aligned to the left, this means the date isn't really a date. It's text. And Excel won't recognize the text as a date. Here are some solutions that can help this problem.

    Check for leading spaces

    Double-click a date that is being used in a subtraction formula.

    Put your cursor at the beginning and see if you can select one or more spaces. Here's what a selected space looks like at the beginning of a cell: Cell with space selected before 1/1/2016

    If your cell has this problem, proceed to the next step. If you don't see one or more spaces, go to the next section on checking your computer's date settings.

    Select the column that contains the date by clicking its column header.

    Click Data > Text to Columns.

    Click Next twice.

    On Step 3 of 3 of the wizard, under Column data format, click Date.

    Choose a date format, and then click Finish.

    Repeat this process for other columns to ensure they don't contain leading spaces before dates.

    Regards,
    Peter

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Strange error message
    By Tony Valko in forum The Water Cooler
    Replies: 36
    Last Post: 12-21-2016, 01:56 PM
  2. [SOLVED] Strange #NAME? Error
    By GarethT in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2013, 11:11 AM
  3. Compile Error - V.Strange
    By dskooo1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2011, 08:27 AM
  4. Strange error
    By Lista in forum Excel General
    Replies: 5
    Last Post: 03-13-2007, 06:37 AM
  5. [SOLVED] strange error
    By AD108 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-09-2006, 04:35 AM
  6. Very strange error
    By superkopite in forum Excel General
    Replies: 3
    Last Post: 02-19-2006, 07:25 AM
  7. [SOLVED] Strange error??
    By Bugman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2006, 08:35 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1