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

 

[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

 

[Jonmo1]

You left off the 2nd half of your IF...

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

An if usually goes something like
If X = Y Then
X is the date in N1119, but You left out the = Y part

if the date in cell N1119<today(), i="" it="" font="" response,<="" for="" 0="" give="" will="" formula="" the="" 2015-10-15="" is="" today="" if="" eg.="" me="" to="" gives="" passed="" search="" that="" day="" and="" automatically="" refresh="" excel="" when="" api="" an="" use="" because="" automatically,="" update="" not="" stop="" next="" C="" 15="" temperature="" 2015-10-14="" date="" N1119="" cell="" in="" eg="" value,="" last="" remain="" change="" calculated="" data="" also=""></today(),> is what??
IF it's equal to today?
If it's in January ?
If it's 30 days in the past?
If it's equal to a date in another cell?
What ???


And the part about keeping the last formula result at the time that criteria is met.
That will likely require VBA code.

 

[vivis93]

here is what i want http://www.excelforum.com/excel-gen...he-last-data-of-formula-calc.html#post4215206

 

[Jonmo1]

So you want it to stop calculating when N1119 is < TODAY()
Should that be permanent, no matter what happens to the date in N1119 in the future?

If so, Try this
Right click the Sheet's Tab, and click "View Code"
Paste the following code there

Private Sub Worksheet_Calculate()
If Range("N1119").Value < Date Then
    Range("M1119").Value = Range("M1119").Value
End If
End Sub

I've assumed the formula you're talking about is in M1119 (you didn't specify where the formula is)
If that's wrong, change the 2 M1119 references to whatever cell actually contains the formula.

 

[vivis93]

the formula is in cell AE7 and repeats after 18 cells, sow the next is on cell AE25, AE43...

 

[Jonmo1]

What is in the between cells (AE8 to AE24, AE26 to AE42) ?
Are they also formulas, or hard coded values?

What is the actual END, last cell ?

 

[vivis93]

some of them are with the same formula but with different xml filter eg. humidity, precipitation, and some are with different formulas, the end is AE3574

 

[Jonmo1]

And they're ALL dependant on N1119, or would AE25 then be dependant on N1137 ?

Try this assuming they're all dependant on N1119

Private Sub Worksheet_Calculate()
Dim i As Long
If Range("N1119") < Date Then
    For i = 7 To 3574 Step 18
        Cells(i, "AE").Value = Cells(i, "AE").Value
    Next i
End If
End Sub

 

[vivis93]

nothin hapend, i dont know if you understand you i will explain it again

with this formula =IFERROR(FILTERXML(WEBSERVICE("http://api.openweathermap.org/data/2.5/forecast/daily?q=New York&mode=xml&units=metric&cnt=14appid=bd82977b86bf27fb59a04b61b657fb6f");"/weatherdata/forecast/time[@day='"&TEXT(N1;"YYYY-MM-DD")&"']/temperature/@day)&" °C";"0")
the api gives data forecast for next 14 days so if the day in the N is lower than present there no data, i use iferror to give me 0 for that.

If today is 2015-10-15
N| AE |if today is 2015-10-19
1 | 2015-10-16 | Formula wint N1 => 21C | 0
2 | 2015-10-17 | Formula wint N2 => 20C | 0
3 | 2015-10-18 | Formula wint N3 => 26C | 0
4 | 2015-10-19 | Formula wint N4 => 12C | 12C
5 | 2015-10-20 | Formula wint N5 => 10C | 10C
...| |

If the present day change i don't want to get the 0 or error from the api of formula in the cell AE but i want to remain the last value eg. 21C, 20C, 26C