Solved: Retaining data from a conditional formula XL

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

Not open for further replies.

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

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.

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.

Joined:
Nov 13, 2010
Messages:
19

Ooops didn't see the subtle change to the formula - will give that a whirl!

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

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.

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

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.

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.

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

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

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.

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.

As Seen On