Insight on a formula
I have an Excel file that I use to track my flights as part of my pilot’s license
I had fun making a formula to automatically calculate the time of each flight spent at night vs in daytime and calculate this according to the position of the departure and arrival aerodromes and the time of year, if anyone has advice especially performance level of the formula im here for it 🙏🙏
=IF(OR(BE26="",BF26="",NOT(ISNUMBER(AU26)),NOT(ISNUMBER(AV26)),NOT(ISNUMBER(AW26)),NOT(ISNUMBER(AX26))),"",LET(dur,(BF26-BE26)*24,n,60,k,SEQUENCE(n),frac,(k-0.5)/n,t,BE26+frac*dur/24,lat,AU26+frac*(AW26-AU26),lon,AV26+frac*(AX26-AV26),jd,t+2415018.5,jc,(jd-2451545)/36525,L0,MOD(280.46646+jc*(36000.76983+jc*0.0003032),360),M0,357.52911+jc*(35999.05029-0.0001537*jc),ec,0.016708634-jc*(0.000042037+0.0000001267*jc),Cs,SIN(RADIANS(M0))*(1.914602-jc*(0.004817+0.000014*jc))+SIN(RADIANS(2*M0))*(0.019993-0.000101*jc)+SIN(RADIANS(3*M0))*0.000289,Ts,L0+Cs,om,125.04-1934.136*jc,Lap,Ts-0.00569-0.00478*SIN(RADIANS(om)),obl0,23+(26+(21.448-jc*(46.815+jc*(0.00059-jc*0.001813)))/60)/60,obl,obl0+0.00256*COS(RADIANS(om)),decl,DEGREES(ASIN(SIN(RADIANS(obl))*SIN(RADIANS(Lap)))),yt,TAN(RADIANS(obl/2))^2,Eq,4*DEGREES(yt*SIN(2*RADIANS(L0))-2*ec*SIN(RADIANS(M0))+4*ec*yt*SIN(RADIANS(M0))*COS(2*RADIANS(L0))-0.5*yt*yt*SIN(4*RADIANS(L0))-1.25*ec*ec*SIN(2*RADIANS(M0))),tst,MOD((t-INT(t))*1440+Eq+4*lon,1440),Ha,IF(tst/4<0,tst/4+180,tst/4-180),alt,DEGREES(ASIN(SIN(RADIANS(lat))*SIN(RADIANS(decl))+COS(RADIANS(lat))*COS(RADIANS(decl))*COS(RADIANS(Ha)))),night,IF(alt<=-6,1,0),dur*SUMPRODUCT(night)/n))
With AU/AV departure airport lat/lon
AW/AX arrival airport lat/lon
BE eparture time utc
BF arrival time utc
[link] [comments]
Want to read more?
Check out the full article on the original site