Stop formula from calculating if criteria is meet and keep the last data of formula calc.

[vivis93]

Hi i have this formula in my cell

[COLOR=#333333]=FILTERXML(WEBSERVICE("http://api.openweathermap.org/data/2.5/forecast/daily?q="&AE1121&"&mode=xml&units=metric&cnt=14appid=bd82977b86bf27fb59a04b61b657fb6f");"/weatherdata/forecast/time[@day='"&TEXT(N1119;"YYYY-MM-DD")&"']/"&AC1126&"/@"&AD1126)[/COLOR]

I want to stop the formula from calculating if the date in cell N1119<Today(), also the data calculated not to change to remain the last value, eg in the cell N1119 the date is 2015-10-14 and the formula gives to me the temperature 15 C the next day the formula to stop and to not update automatically, because i use an api when excel refresh automatically and if the day that i search is passed it gives to me 0 eg. if today is 2015-10-15 the formula will give 0 for response,
I use this formula in different cells.

Any help

 

[Jonmo1]

So if N is less than today's date, you want it to proceed as if N was equal to today ?

Try changing this part
TEXT(N1;"YYYY-MM-DD")
to
TEXT(MAX(TODAY(),N1);"YYYY-MM-DD")

 

[vivis93]

This http://api.openweathermap.org/data/2.5/forecast/daily?q=new%20york&mode=xml&units=metric&cnt=14appid=bd82977b86bf27fb59a04b61b657fb6f gives forecast from present day to 14 days, when formula in cell AE search for date in cell N it gives me the temperature for that day, i want this value the next day to not refresh or to not change to 0 to keep the last value eg. 20C,the temperature to remain according to exact day not to max of today and date in cell N, do you get it

 

[Jonmo1]

do you get it

Obviously I don't. Sorry.

Maybe someone else understands.

 

[Jonmo1]

So if N is less than today's date, you want it to proceed as if N was equal to today ?

Is this a correct assessment of what you want?

 

[vivis93]

ok thank you very much for trying to help yaeah thats all i need i hope some one will help all the best

 

[Jonmo1]

If that was a correct assessment, then that's what my suggestion will do.

MAX(TODAY(),N1)

If N1 is less than today, it will use Today's date.
If N1 is today or in the future, it will use N1.

I hadn't noticed that your formula has ; instead of , as seperators.
So you would need to change that , to a ;

 

[vivis93]

thank you again but this wont solve my problem