# What's going on with this Excel formula? **URL:** \ **Category:** Factual Questions\ **Created:** [July 29, 2007, 2:40pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588 "2007-07-29T14:40:10Z")\ **Posts on this page:** 15\ **Page:** 1
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 29, 2007, 2:40pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/1 "2007-07-29T14:40:10Z")
I’m hoping someone can let me know what is wrong with the following VBA formula. I have purposefully made the formula identical in both cases as it better illustrates the problem. ```auto If Sin((Lon2) - (Lon1)) = 0 Then CalcTrueCourse2 = WorksheetFunction.Acos((Sin(Lat2) _ - Sin(Lat1) * Cos(D)) / (Sin(D) * Cos(Lat1))) _ * 180 / WorksheetFunction.Pi Else CalcTrueCourse2 = WorksheetFunction.Acos((Sin(Lat2) _ - Sin(Lat1) * Cos(D)) / (Sin(D) * Cos(Lat1))) _ * 180 / WorksheetFunction.Pi End If ``` My problem is that the CalcTrueCourse2 formula works correctly when a number other than 0 is returned for Sin((Lon2)-(Lon1)) but gives a #Value error when returning 0. Surely the result should be identical? If it helps, this is the entire function with the original formula: ```auto Public Function CalcTrueCourse(varLat1, varLon1, varLat2, varLon2, varDist) Dim Lat1 As Single Dim Lat2 As Single Dim Lon1 As Single Dim Lon2 As Single Lat1 = WorksheetFunction.Radians(ConvToDecDeg(varLat1)) * -1 Lat2 = WorksheetFunction.Radians(ConvToDecDeg(varLat2)) * -1 Lon1 = WorksheetFunction.Radians(ConvToDecDeg(varLon1)) * -1 Lon2 = WorksheetFunction.Radians(ConvToDecDeg(varLon2)) * -1 D = varDist * WorksheetFunction.Pi / 60 / 180 If Sin((Lon2) - (Lon1)) < 0 Then CalcTrueCourse = WorksheetFunction.Acos((Sin(Lat2) _ - Sin(Lat1) * Cos(D)) / (Sin(D) * Cos(Lat1))) _ * 180 / WorksheetFunction.Pi Else CalcTrueCourse = 2 * WorksheetFunction.Pi _ - WorksheetFunction.Acos((Sin(Lat2) - Sin(Lat1) _ * Cos(D)) / (Sin(D) * Cos(Lat1))) _ * 180 / WorksheetFunction.Pi End If End Function ``` I can post the other dependant functions if required or email the spreadsheet. ---
**Author:** ![intention](https://avatars.discourse-cdn.com/v4/letter/i/e95f7d/32.png) [@intention](https://boards.straightdope.com/u/intention)\ **Post date:** [July 29, 2007, 8:46pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/2 "2007-07-29T20:46:32Z")
Well, I messed with it and can’t get it to work at all. What is the “varDist” variable? Seems like if you want the course from point A to point B, that’s all the information you need. w. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 29, 2007, 10:19pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/3 "2007-07-29T22:19:26Z")
It needs the latitude and longitude of each position plus the distance, all in radians (i.e., the angular distance.) It will only work out the course, the distance is a seperate formula, though there is another formula that will do both course and distance. I got the formula from [http://williams.best.vwh.net/avform.htm](http://williams.best.vwh.net/avform.htm). ---
**Author:** ![N9IWP](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/n9iwp/32/3154_2.png) [@N9IWP](https://boards.straightdope.com/u/N9IWP)\ **Post date:** [July 29, 2007, 10:44pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/4 "2007-07-29T22:44:00Z")
Is there a division by zero going on? Does the original function work for both \> 0 and \< 0 ? (i.e., only fails when = 0)? Brian ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 29, 2007, 11:18pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/5 "2007-07-29T23:18:31Z")
There is no division by zero as far as I can see. The longitude is only required to decide which of the two formula’s to use, so the zero should just be telling it to use the second formula. Right I’ve narrowed it down a little. The problem only occurs when the distance is 60nm, and the longitudes are the same (i.e., the course is either north or south.) It occurs without the longitudes in the formula. So if I give it two latitudes of say 10.0S and 11.0S and tell it the distance is 60nm (which implies a course of south,) it doesn’t like it, but if I increase the distance, it works ok. It also works if I make the lats 10.1S and 11.0S with a distance coincident with a course of south. So perhaps there is some dividing by zero going on. Unfortunately I don’t fully understand the formula, mainly because I haven’t done trig for 15 years and I can’t be bothered learning it again. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 29, 2007, 11:26pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/6 "2007-07-29T23:26:00Z")
I think possibly the SIN of the distance is getting rounded to zero. And yeah, the original only failed at zero, but that particular zero isn’t getting used in the formula itself. ---
**Author:** ![N9IWP](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/n9iwp/32/3154_2.png) [@N9IWP](https://boards.straightdope.com/u/N9IWP)\ **Post date:** [July 30, 2007, 12:14am UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/7 "2007-07-30T00:14:09Z")
Well sin(0) = 0 Does it fail if the distance is LESS than 60nm? If so, then it is probably a round off issue. What is D declared as? Brian ---
**Author:** ![intention](https://avatars.discourse-cdn.com/v4/letter/i/e95f7d/32.png) [@intention](https://boards.straightdope.com/u/intention)\ **Post date:** [July 30, 2007, 12:43am UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/8 "2007-07-30T00:43:48Z")
[QUOTE=1920s Style “Death Ray”] I think possibly the SIN of the distance is getting rounded to zero. And yeah, the original only failed at zero, but that particular zero isn’t getting used in the formula itself. [/QUOTE] This could be the reason. Try changing all of the DIM … AS SINGLE to DIM … AS DOUBLE. w. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 30, 2007, 2:03am UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/9 "2007-07-30T02:03:27Z")
[QUOTE=N9IWP] Well sin(0) = 0 Does it fail if the distance is LESS than 60nm? If so, then it is probably a round off issue. What is D declared as? [/QUOTE] It fails only when the distance is exactly 60 or 120 but is ok with a distance of 180. D isn’t declared as anything yet. Part of the problem is that not only do I not understand the formula, I’m also new to VBA and don’t always understand that either (for example, I’m not entirely clear on what to use the various different number types for, single, double, long, etc.) I think I’m going to have to do a bit of study. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 30, 2007, 8:53am UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/10 "2007-07-30T08:53:29Z")
I’ve solved the problem, or rather, I’ve used a different formula that doesn’t require a distance input and that doesn’t seem to have this problem. ---
**Author:** ![intention](https://avatars.discourse-cdn.com/v4/letter/i/e95f7d/32.png) [@intention](https://boards.straightdope.com/u/intention)\ **Post date:** [July 30, 2007, 6:00pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/11 "2007-07-30T18:00:24Z")
[QUOTE=1920s Style “Death Ray”] I’ve solved the problem, or rather, I’ve used a different formula that doesn’t require a distance input and that doesn’t seem to have this problem. [/QUOTE] Makes sense, that’s why I asked why the formula required a distance input. w. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 31, 2007, 8:02am UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/12 "2007-07-31T08:02:05Z")
[QUOTE=intention] Makes sense, that’s why I asked why the formula required a distance input. w. [/QUOTE] The spreadsheet has a separate distance column. Because the distance is already supplied, I used the formula that has a distance input. It didn’t really matter which I used (apart from the glitch.) ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 31, 2007, 3:17pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/13 "2007-07-31T15:17:17Z")
[QUOTE=1920s Style “Death Ray”] Part of the problem is that not only do I not understand the formula, I’m also new to VBA and don’t always understand that either (for example, I’m not entirely clear on what to use the various different number types for, single, double, long, etc.) [/QUOTE] You’ve got a workaround so maybe you don’t care anymore, but I was trying to troubleshoot this and found that ConvToDecDeg is undefined. Is that another function that you wrote yourself? What units are the parameters for lat & long in, and why are you converting them to degrees then to radians? Not that I think that’s a problem, just don’t understand what you’re doing. I checked your reference site and didn’t see that in their formula. ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 31, 2007, 11:30pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/14 "2007-07-31T23:30:34Z")
ConvToDecDeg was written by me. It converts a latitude or longitude from degrees minutes and decimal minutes (DDMM.mm) as generally used by GPS units to decimal degrees (DD.dddd) which is easier to work with. It also didn’t quite work properly, as it didn’t handle single digit degrees. I’ve fixed that by changing the latitude/longitude input format to DD.MM.mm which forces the cell to be text and maintains leading zeros, allowing my simple formula, that made use of the LEFT() and MID() functions, to work. Another quick question while you are here, is there a way to keep leading zeros without making the cell text? I know you can use format to display leading zeros but the underlying number doesn’t have them. Anyway, if you’re still interested in trouble shooting it, the kind of position it wasn’t working for was say 10:00.0S 133:00.0E 11:00.0S 133:00.0E ConvToDeDeg would just make it 10.0S 133.0E 11.0S 133.0E ---
**Author:** ![Richard\_Pearse](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/richard_pearse/32/13144_2.png) [@Richard\_Pearse](https://boards.straightdope.com/u/Richard_Pearse)\ **Post date:** [July 31, 2007, 11:38pm UTC](https://boards.straightdope.com/t/whats-going-on-with-this-excel-formula/413588/15 "2007-07-31T23:38:33Z")
Oh, and south positions are negative and the “S” and “E” wouldn’t be there so, -10.0 133.0 -11.0 133.0