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.

 

[ollyhughes1982]

You can see they are set to a max range of ro 67 at the moment, which works as there are no gaps until 68, if you change it 68 or more then there is an error

 

[kweaver]

On the Misc sheet, if I change EQ3 to:

=IF(OR(E68="",F68=""),"",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)),""))

There is a blank (i.e., "") in the cell versus an error.

My confusion is why the formula is in EQ3 with an absolute cell reference in the Radian calculations. Would you continually change the 68 to the next value (e.g., 69) for future calculations?

 

[ollyhughes1982]

On the Misc sheet, if I change EQ3 to:

=IF(OR(E68="",F68=""),"",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)),""))

There is a blank (i.e., "") in the cell versus an error.

My confusion is why the formula is in EQ3 with an absolute cell reference in the Radian calculations. Would you continually change the 68 to the next value (e.g., 69) for future calculations?

No, it will always be 10002 instead of 67 in each case; I just put 67 in to illustrate that it works until it encounters a "" cell

 

[kweaver]

Now I get it. I don't know the formula well enough to tell you what you should be multiplying by [ e.g., SIN(RADIANS($E$3:$E$10002) , etc.]
Maybe this:

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

 

[ollyhughes1982]

Now I get it. I don't know the formula well enough to tell you what you should be multiplying by [ e.g., SIN(RADIANS($E$3:$E$10002) , etc.]
Maybe this:

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

Thanks, I’ve had to pop out for a bit now, but will try later tonight or in the morning, as soon as I can. Thanks for your help so far.

 

[ollyhughes1982]

Thanks, I’ve had to pop out for a bit now, but will try later tonight or in the morning, as soon as I can. Thanks for your help so far.

I've tried that, but I get 3,325.73549294 K. This is too far

 

[kweaver]

I'm sorry, Olly, but I'm out of suggestions. Maybe another math and Excel person sees something I missed.

 

[ollyhughes1982]

I'm sorry, Olly, but I'm out of suggestions. Maybe another math and Excel person sees something I missed.

No problem, thanks for your help. I wish I could understand the formula better myself!

 

[ollyhughes1982]

Also posted on Excel Forum: Ignore blanks in RADIANS distance calcluations

 

[ollyhughes1982]

This has now been resolved in the Excel Forum post.

It worked with the addition of two LET functions within the formulas.

The formula for the original column is:

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

The adjacent formula is as follows:

=IF(B3<>"",LET(a,FILTER($B$3:$B$10002,$B$3:$B$10002<>""),b,FILTER($E$3:$E$10002,$E$3:$E$10002<>""),c,FILTER($F$3:$F$10002,$F$3:$F$10002<>""),IF(B3<>"",(INDEX(a,MATCH(EQ3,ACOS(SIN(RADIANS(E3))*SIN(RADIANS(b))+COS(RADIANS(E3))*COS(RADIANS(b))*COS(RADIANS(c-F3))-1*10^-14)*6371,0))),"")),"")

Thanks.