1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Retaining data from a conditional formula XL

Discussion in 'Business Applications' started by harvey09, Nov 25, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    G'Day Guys,
    I'm wanting to automate a small spreadsheet I have, to put in a certain monetary value in a cell at the start of the month.

    I made up a conditional formula and was able to get it to work, but of course, as soon as the next day ticks over, the formula is no longer valid, and the cell reverts to being empty.

    Is there a fairly simple way to put something in place, so that once the condition is met, the value is retained?

    My formula might not be ideal, and I'm happy to receive info on a better way to do this.

    The conditional formula is: IF(A2=(TODAY()),A3,"") where A2 contains my trigger date, and A3 contains the Dollar value that I want to retain, once the formula gets a true condition.

    Hope you can spare some time to offer advice.

    Cheers Ron
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Would this do it?


    IF(A2<=(TODAY()),A3,"")


    this says the condition is met when A2=today and beyond.
     
  3. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    That's funny because if the date in A2 is true to "Today", the value in A3 will now show in A1, (where the formula resides.)

    If I now change the date in A2 to the day after what today date is, the Null value for the formula is used. (ie False) which is why I posted here for help.
     
  4. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19

    Ooops didn't see the subtle change to the formula - will give that a whirl! :)
     
  5. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    Just changed the < to > and it worked a treat thanks for that.

    I did notice that when you check the fx button next to the contents of the cell, on the formula bar, it says in the formula breakdown that the "logical test is volitile. Does this mean the results won't be reliable?
     
  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi

    are you certain about the change? The way you have it the number will appear any day up to and including today and will disappear tomorrow and beyond. Is that what you were going for?


    Volatile means only that the result is variable, in this case depending on the day. A volatile cell is always recalculated at each recalculation even if it does not appear to have any changed precedents.
     
  7. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    No you were right, and sorry for posting the change, it's just when I tested it in the play spreadsheet reversing the inequality sign seemed to work.

    Now that I've just put it in the spreadsheet I want to use, it went crazy, so I changed it back to what you posted, and after a couple of edits of the trigger date it seemed to settle down and do what I wanted.

    So does all this recalculating due to the volatile tag, bog things down, as I'll be using 24 of these formulas for the year?

    Thanks for your help, most appreciated

    Ron
     
  8. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    24 shouldn't be a problem at all. Assuming you have a relatively modern set-up Excel can handle much more without any noticeable lag.
     
  9. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    It's interesting that I've been able to get your original formula to work in the spreadsheet I want to use, yet I have to change the inequality sign in my test spreadsheet for it to give me the desired result - weird. I have no idea why, but for the moment all's testing okay in the main spreadsheet, so I guess that's all that matters.


    Thanks XCubed for your help.
     
  10. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    All I can think of is that you may have the wrong year in A2 in your test file. If that's not the case I'd be happy to look at it if you can upload the test file.
     
  11. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    Thanks for the offer XCubed but the formula is working as I need it in the spreadsheet I want, besides I don't want to be wasting your time.

    I think my biggest confusion is that I'm reading IF(A2<=(TODAY()),A3,"") as, if A2 is less than or equal to today then do A3, otherwise do blank entry. Where I think it means if Today is greater than or equal to A2 etc. Is that the case or are my maths up the creek? :)
     
  12. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    It is a bit confusing but the 2 alternate meanings you mention are actually the same thing


    if A2 is less than or equal to today [is equal to] if Today is greater than or equal to A2


    I try to think of it in words i.e.


    If the date in A2 is earlier than or equal to today [<=] then the condition is met
    If the date in A2 is after today's date then then the condition is not met


    Help? or more confusing?


    Check your dates in the test file and if A2 is anything before 27 November 2013 (Today in Australia) then you should be getting a result.


    Also make sure that the date in A2 is in proper Date Format.
     
  13. harvey09

    harvey09 Thread Starter

    Joined:
    Nov 13, 2010
    Messages:
    19
    The confusion is my reading of the > & < symbols in the Formula eg. if x<y then...... In words. IF X less than Y then do False condition else do True condition. I'm starting to get a headache. LOL

    Bottom line is, your modification to my formula works a treat and I thank you for that, and because it does work I should just go with the flow an accept it, rather than question why. :)
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1113916

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice