'Getting random errors upon caluculating and recalculating formula containing FILTERXML and WEBSERVICE with bingmaps since switching to 365
I have three columns with formulas providing the latitude, longitude, and travel time to that destination respectively, using FILTERXML and WEBSERVICE with a Bingmaps API in a travel planning workbook that used to work perfectly in Excel 2013, apart from Bingmaps always underestimating travel times by a factor of around 0.8 (but Bingmaps API is free and easier to integrate than google maps).
Ever since I "upgraded" to office 365, two things have happened:
- Excel changed FILTERXML to @FILTERXML
- These three columns produce random errors every time they are recalculated. I.e. with every recalculation, there is a chance any given cell will display a VALUE error. Which cells these are seems random and changes with every recalculation.
- Activating the error cell and hitting enter will always produce the correct result.
- Trying the "Text to columns" trick to activate each cell in the column doesn't work as expected and results in new random VALUE errors across all three columns, not just the activated one.
- Which cells in these three columns produce errors is not correlated to their rows. I.e. there might be an error in column 3 but not in columns 1-2. There will always be an error in column 3 if there is an error in either 1 or 2 because 3 is dependent on the preceding columns.
In the picture below you can see an example. Note that in the third column (Travel Time), Errors have been set to display blanks.
Here are the formulas, in order:
=IF(OR($G62="",$W62=FALSE,$W62=""),"",@FILTERXML(WEBSERVICE("dev.virtualearth.net/REST/v1/Locations?countryRegion="&$H62&"&adminDistrict="&$I62&"&locality="&$G62&"&maxResults=1&o=xml&key="&BingKey),"//Latitude[1]"))
=IF(OR($G62="",$W62=FALSE,$W62=""),"",@FILTERXML(WEBSERVICE("dev.virtualearth.net/REST/v1/Locations?countryRegion="&H62&"&adminDistrict="&$I62&"&locality="&$G62&"&maxResults=1&o=xml&key="&BingKey),"//Longitude[1]"))
=IF(OR($G62="",$W62=FALSE,$W62=""),NA(),@FILTERXML(WEBSERVICE("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="&LatOrigin&","&LongOrigin&"&destinations="&$J62&","&$K62&"&travelMode=driving&o=xml&key="&BingKey),"//TravelDuration[1]")/1440)
Can anyone help me figure out what is going wrong here, and what changed from 2013 to 365 to produce these errors in previously working formulae?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

