Ignore blanks in RADIANS distance calcluations

[ollyhughes1982]

Hi,

I have the following formula, which is calculating the set of coordinates that are closest to another given set of coordinates, from a list:

=IF(B3<>"",(SMALL(ACOS(SIN(RADIANS(E3))*SIN(RADIANS($E$3:$E$67))+COS(RADIANS(E3))*COS(RADIANS($E$3:$E$67))*COS(RADIANS($F$3:$F$67-F3))-1*10^-14)*6371,2)),"")

This formula works up until row 67, but then if I expand this downwards further into my range (where there are blanks, e.g. row 68 has a blank in E68) it gives me a value error. I want to expand my range to $E$3:$E$10002 (the extent of my range). So, what I need to know is, is there way to make the formula ignore blank entries and calculate from all populated ones?

FYI - The "blanks" I talk about are "" results from a formula, not true blanks - so there are formulas in those cells.

Thanks in advance!

Olly.

 

[kweaver]

Maybe do an initial test on the changing E column: =IF(or(E3="",F3=""),whatever,your trig formula)

 

[ollyhughes1982]

Maybe do an initial test on the changing E column: =IF(or(E3="",F3=""),whatever,your trig formula)

I have tried a few things like this, but just can't get anything to work. Thanks.

 

[kweaver]

Is the sine or cosine undefined for one of those cases?

 

[ollyhughes1982]

Is the sine or cosine undefined for one of those cases?

I'm not sure tbh. I found this formula online and I am not fully understanding of how it works. I know it does, as I have manually checked results, but i just cannot get it to ignore blank entries in E and F cells

 

[kweaver]

Did you change the absolute reference: RADIANS($E$3:$E$68) ?
I tried your formula with E69 and F68 blank and it works. But when there's a "" in either it doesn't. So, I think that's the issue.
Using this:

=IF(B3<>"",(SMALL(ACOS(SIN(RADIANS(E3))*SIN(RADIANS($E$3:$E$68))+COS(RADIANS(E3))*COS(RADIANS($E$3:$E$68))*COS(RADIANS($F$3:$F$68-F3))-1*10^-14)*6371,2)),"")

I get a result of 0.000901

(BTW: there aren't any values where the sine and cosine are undefined)
I have used a similar formula to calculate the distances between two locations given the longitude and latitude of each...but they were all valid locations.

 

[ollyhughes1982]

I have attached a link to my example, if that helps? Thanks for your help! Example.xlsx

 

[kweaver]

Unfortunately, I can't seem to scroll through the sheet and see the values on the screen. But, I can change the name box.
I don't see E68 or F68 as "".

 

[kweaver]

I also don't see that exact formula anywhere. In what cell is it supposed to be?

 

[ollyhughes1982]

Hi. The formula is in EQ3, ET3, EW3, EZ3 etc. Each is for the 1st, 2nd, 3rd etc. closest ones. You should just be able to scroll as normal, only the top 2 rows are frozen.