+ Reply to Thread
Results 1 to 11 of 11

Part of formula mysteriously not working

  1. #1
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Part of formula mysteriously not working

    A little help on a couple things pls:

    The issue is in the pink. The "if(datelisted2... " part of my formula doesn't seem to be working under the "(dom)" column. You'll notice the last "test" row I remove the "if(dom<>0..." and the trouble part of the formula elsewhere now works. What is this all about?

    And how would you erase to the right the infinite black background formatting in those rows lol? They have been nagging me forever. I guess I selected whole rows and did that.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Part of formula mysteriously not working

    It appears to me that the error in the (DOM) column (column E) is simply propagating the error in the DOM column (column G). The error in the DOM column is because the sold date2 column is not blank, it contains null string "". The DOM column tries to subtract a number from a text string, doesn't know how to do that, so it returns an error. Sold date2 appears to be a time stamp formula, so the null string is a place holder for "an 's' has not been entered into tested"

    My guess is that the flow of logic through each row is not exactly what you really want. At present, the logic flow appears to be:
    If tested is "L," then sold date2 will be null string. DOM will be an error as it attempts to subtract a number and text. The error in DOM propagetes to (DOM).
    In the rows where tested is "s", sold date2 will have a date/number in it, DOM will have no trouble subtracting two numbers, and that value propagates to (DOM).
    Your "fixed" entry in the test row removes all reference to DOM and simply returns the difference between TODAY() and date listed2

    What logic do you want the row to have when tested is "L" that avoids the error?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Part of formula mysteriously not working

    To get rid of the black background formatting, I selected the cells from the table right to the edge of the spreadsheet -> Home -> Editing ribbon Clear command -> Clear Formats.

  4. #4
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: Part of formula mysteriously not working

    ah, okay, so then how do I ask excel to check for what looks like "nothing" to me? if(sold date=""?
    I shall try that...

    thanks
    Excel 1.0.1 (16.0.14326.20140) (Android)
    Excel 2010 14.04.4760.1000

  5. #5
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: Part of formula mysteriously not working

    What I want is for it to give me today less the day the item was listed, to show the "days on market"(dom), UNLESS over at the end of the table "DOM" is already populated(from completed sales showing ultimate "dom"), in which case it just displays what's under "DOM". Hope that was more clear.

  6. #6
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: Part of formula mysteriously not working

    see how it works in row 15? see the change I made in the formula. So both versions confirm they both work but not together for some reason.

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Part of formula mysteriously not working

    I'm not sure if it is more clear or not. My understanding would be that you want DOM to be the difference between date listed and either today or sold date2. I would be inclined to use something like MIN() to choose in DOM. =MIN(TODAY(),E2)-C2 [or whatever the equivalent is in structured table references]. While tested is L, E2/sold date2 will be a text string, and MIN() will ignore the text string, returning TODAY(). Once testing is changed to s, sold date2 will have a date/number entered, and MIN() will choose the earlier of the two dates.

    Of course, I am assuming that time will flow forward naturally (no Star Trek temporal anomalies) so that sold date2 will never be after TODAY(). If you are not comfortable with that assumption, replace the MIN() function with a more robust IF() function to choose between TODAY() and sold date2 when taking the difference.

  8. #8
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468
    Quote Originally Posted by MrShorty View Post
    I'm not sure if it is more clear or not. My understanding would be that you want DOM to be the difference between date listed and either today or sold date2. I would be inclined to use something like MIN() to choose in DOM. =MIN(TODAY(),E2)-C2 [or whatever the equivalent is in structured table references]. While tested is L, E2/sold date2 will be a text string, and MIN() will ignore the text string, returning TODAY(). Once testing is changed to s, sold date2 will have a date/number entered, and MIN() will choose the earlier of the two dates.

    Of course, I am assuming that time will flow forward naturally (no Star Trek temporal anomalies) so that sold date2 will never be after TODAY(). If you are not comfortable with that assumption, replace the MIN() function with a more robust IF() function to choose between TODAY() and sold date2 when taking the difference.
    Sorry but I'm so "beginner" I barely followed that. But it looks like you get what I want. But why doesn't it work? I want to know what is wrong with my formula that it works but not all together. But I will try your suggestion. Am I asking excel too complicated a question? I'm asking for it to to one of two simple instructions depending on 1 of two simple scenarios with the if/then function. How is it getting so complicated?

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Part of formula mysteriously not working

    I doubt the question you are asking Excel is too complicated. Spreadsheets, like other programming languages, can handle some pretty complex logic. I find that the hardest is usually getting the logic clear in my own head so that I can keep it straight when programming the spreadsheet formulas.

    The logic that I see in the current sequence of formulas for each row:

    1) Start with inputs, which are "tested" (column B) and date listed2 (column C). B will be either "s" or "L", C will be a date/number.
    2) Sold date2 (column F) looks at B. If B is "s," then F will be the date that "s" was entered. If B is anything else, F will be null string.
    3) DOM (column G) takes the value of F (without any error trapping) and subtracts the date/number in C from F. Without error trapping, when F is null string (when B is not "s"), this will result in an error.
    4) (DOM) (column E) tests G.
    4a) If G is not 0, then it returns the value of G.
    4b) If G is 0, then subtract column C from today's date. Without error trapping, errors raised in G will simply propagate to E. Based on the current logic, G will only be 0 when the dates in F and C are the same.

    The logic in row 15:
    1) Same inputs.
    2) F is still the same
    3) G is still the same
    4) E tests C (note that E is not related at all to B, F, or G)
    4a) If C is not 0, then subtract C from today's date.
    4b) If C is 0, then copy C. I am not sure when C would be 0, except when it is empty.

    I don't think either of those logic sequences represents, "If sold, calculate the difference between sold date and listed date. If not sold, calculate difference between today and listed date." This logic might be:

    1) Inputs are still B and C
    2) F is still the same
    3) Keeping with If..then logic, G needs to test B.
    3a) If B is "s", then subtract C from F
    3b) If B is "L", then subtract C from today
    3c) In an Excel IF() function, this might be =IF(B2="s",F2,TODAY())-C2.
    4) E can simply copy G =G2.

    In my early programming days (and even today, when my logic is particularly complex), I would use paper and pencil or similar to write out my logic in decision trees, truth tables, or other device for organizing my thought process before sitting in front of the computer to program. How would you write out the logic that you want to use here?

  10. #10
    Forum Contributor juntjoo's Avatar
    Join Date
    02-29-2008
    Location
    Cape Coral, FL
    MS-Off Ver
    Excel 2010 14.04.4760.1000
    Posts
    468

    Re: Part of formula mysteriously not working

    Try this. I took out distractions. No more column B. But as you can see it still works on the bottom #15 row when I take the other part of the formula.

    https://www.dropbox.com/s/b42f0r73xv...tion.xlsx?dl=0

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Part of formula mysteriously not working

    I'm not sure what you want me to see. The logic I see in this file:

    1) Inputs are column A (a date) and column D (another date). Column B appears to be a copy of column D, but is not used anywhere.
    2) Formula in column E subtracts A from D. When there is a date in D, then this will return the correct difference. When D is empty, Excel will treat it as 0, so you get a large negative number.
    3) Column C tests column E to see if it is not 0.
    3a) If E<>0, then copy column E.
    3b) If E=0, then test if column A is not 0.
    3b1) If A<>0, then subtract A from TODAY().
    3b2) If A=0, then copy A.

    In row 15, you have the same logic you had before. If date listed2 (now A) is not 0, subtract A from TODAY(). If A is 0, then copy A.

    If you were to write out the logic you want for this sequence of calculations, how would you write it out?

+ 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. [SOLVED] Formula using mid and length not working in the last part of formula - but not sure why
    By mightybracket123 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-22-2021, 07:59 PM
  2. [SOLVED] Formula Help : IF(ISBLANKK) - Second part not working
    By Logit in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-13-2018, 09:18 PM
  3. Format a part of a text working with only value not working with formula result
    By HaroonSid in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2017, 05:41 AM
  4. [SOLVED] All formulas mysteriously stopped working
    By bawlmer in forum Excel General
    Replies: 11
    Last Post: 02-02-2016, 04:24 AM
  5. Excel 2007 Formula With {} Brackets That Mysteriously Appear and Disappear
    By Derek_FedCiv_HsvAL in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-18-2014, 03:57 PM
  6. Excel features mysteriously no longer working
    By GordonPSmith in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-18-2011, 03:58 PM
  7. 2nd part of formula not working
    By dbl in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2005, 03:05 PM

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