dimanche 16 septembre 2018

Trying to use IF AND with IMPORTXML to return "0" when input cells are blank

I keep getting an "#ERROR!" for the following formula:

=IF(AND(B12="",C12=""),"0","=importxml("http://maps.googleapis.com/maps/api/directions/xml?origin=" & B12 & "&destination=" & C12 & "&sensor=false&alternatives=false","leg/distance/value")")

Everything appears fine but the forward slashes immediately following "http" are telling me "We cannot parse the operator in this context."

I'm using this formula to calculate the distances between two addresses for employee expense reports.

Using only the importxml works perfectly -- it calculates the distance between two addresses accurately.

However, when the "origin" and "destination" cells are blank I need the output to be either blank or zero in order to calculate aggregate miles multiplied by mileage rate for the final report.

Can someone please point out what I'm doing wrong? I've cobbled this formula together by searching through various articles/videos etc. but I can't seem to make the IF statement work with the importxml for blank cells.

I've tried IF AND, IF OR, IF ISBLANK etc. The only iteration that has given me the least errors is the formula copied above. The only hurdle I'm facing is the error saying sheets cannot parse those two forward slashes in this context.

Aucun commentaire:

Enregistrer un commentaire