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? so that I can use the full range down to 10002.

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

Example file can be found here: https://1drv.ms/x/s!AtIvkRgReIvwjx3i...fMvEI?e=c6ySJK

Also posted on Mr.EXCEL: https://www.mrexcel.com/board/thread...2#post-6048615

Thanks in advance!

Olly.

[AliGW]

Please attach your workbook directly here using the attachment facility so that we don't have to follow external links. Thanks.

You could try this (UNTESTED):

=LET(a,FILTER($E$3:$E$67,$E$3:$E$67<>""),b,FILTER($F$3:$F$67,$F$3:$F$67<>""),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)),""))

[ollyhughes1982]

Hi, thanks, I did try that but kept getting a message that my file is too large. It's 14.4mb - what is the limit, please? i can try and do a cut-down version. Thanks.

[AliGW]

1MB - you will need a SAMPLE workbook (cut-down).

Did you try my suggestion?

[ollyhughes1982]

I am just trying it now, thanks.

[AliGW]

OK - let us know.

[ollyhughes1982]

Yes, I believe that it is working, as it is correctly picking up that Bushy junior parkrun (orange placemarker) is approximately 1km away from Bushy parkrun (which is what this formula is calculating for). The only issue I am finding now is that my adjacent formula which finds the corresponding event name for the distance is being thrown out and giving an N/A result. I will do a cut-down version with your new formula included and you'll be able to see, Thanks.Attachment 825997

This is the current formula in the adjacent cell: =IF(B3<>"",(INDEX($B$3:$B$67,MATCH(EQ3,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,0))),"")
Attachment 825998

[AliGW]

I will have a look if you provide a sample (cut-down) workbook directly here. I am afraid that I never follow external links - sorry.

You could apply the same FILTER treatmet to the other formula - did you try that?

[ollyhughes1982]

Yes, no problem. Hopefully that's now attached.

[ollyhughes1982]

Hi. Did the attachment work ok?

[AliGW]

Sorry, I've been out in the garden doing a few jobs.

I've had a look and cannot see where you have used the original updated formula I gave you, so I'm a bit confuded as to what the issue is. Neither have you given any expected results - all I see are error messages.

[ollyhughes1982]

Hi. I have now had a better look and your LET solution for the adjacent column and it works perfectly. It does the calculations and ignores the "blanks". The 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))),"")),"")

The final formula in 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)),"")),"")

These now work perfectly. Thank you.

[AliGW]

Oh, fab. Thanks for letting me know.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[ollyhughes1982]

Both done. Thanks very much for the help.

[AliGW]

Not a problem - sorry I went AWOL for a while! The weather is too good not to be outside.

[ollyhughes1982]

No problem, the weather is terrible today, here in South Wales!