Hey this really has noting to do with aviation only my logbook. See when I started flying I made and Excel Log book and over the years I’ve added things to it to give me information I may want. Here is where I need help. If anyone out there has very good knowledge with Excel and formulas PLEASE LET ME KNOW. There are a few formulas kicking my ass I need help with.
Without looking at the full spreadsheet, I can offer some advice.
I would use conditional formatting to indicate currency.
Depending on how the worksheet is setup, I’d use the sumif function to determine currency. Basically, it the formula would say sum all currency indicators within the past 180 days. If this number is 8 or more, the the conditional formatting will make the cell green other it will be red.
From my experiences, the If statement becomes more powerful as you can define “a data range within a range of cells” but my Excel expertice is not strong enough to even tackle this one.
But since we have a so called self proclaimed Excel expert in this thread, I will defer to him. Please note, he is not a pilot and probably is not aware of the nuances of instrument currency in FAR 61.57. So, validate your excel worksheet with an electronic logbook program if possible.
Vlookup is one of the handiest and most overlooked functions in Excel. I use it daily from simple looks to ensuring data in one place is or isn’t in another place.
I have admitted I am not as good as you, Allen, because I’m not a pilot and therefore not a god. However, given the needed output for the given input, anyone with a good knowledge of Excel can give what is needed, regardless of the field.
For your information, I have used Excel since it was a DOS program. I work with it every day of the week, both at work and at home. I have created spreadsheets that range from the very simple to highly complex spreadsheets. In my company, I am the point person for anyone who has a problem with Excel - or Word or Access or any number of other programs.
Yes, I do know my limits and, once again, you didn’t read my posting completely. I said if I’m given the data needed for both input and output, anyone with a good knowledge of Excel can create what is needed.
Is it your life’s aim to constantly put me done, Allen?
I ask you again, do you know the instrument currency requirements??? You still have not answered this question. Did you even look it up to verify the proposed formula is correct?
If you don’t know the data needed and don’t know the nuances, then your quality is less then desirable if you can’t even validate your own work.
At the risk of repeating myself, magic’s proposed formula will not work for accuracy of currency. I gave you the needed data in my last post, but apparently you did not take the time to read my post.
If you plan to take this on, then you need to know the requirements.
If you dont know the nuances of the instrument approach, then you are not doing any justice in even attempting the formula. If you accept magic’s formula, then you are making a flawed spreadsheet.
I don’t care if you created Excel, if you don’t know the background of your data, then you lose credibiility in what you put out.
I sure hope QUALITY your work product at your workplace is not like what you are posting on this thread, because if it is, I’d suspect just because you know the mechanics of Excel doesn’t make your output of your product right.
Your Excel experience by the way parallels mine, so no, I am not impressed on your background. What is more disconcerting is that you would take on a project and not even know if your output was correct or not.
As you can see, I didn’t take this one on, as I don’t know that I can make a formula with the variables needed to establish currency because of all the nuances involved with the currency requirements and instrument approaches…
Indeed, not only do I recognize my limits, I acknowledge them.
I apologize. Allen has this thing about him always being right (I guess it’s because he’s a pilot and retired government worker so he is god). I have the habit of defending myself and others against bullies and, so, I reply to his statements.
Again, I apologize. I am honestly trying to not to reply to that man.
Reply to the banter thread? What you said, deef1999, should go with the topic it concerns, not the banter thread unless the OWNERS of the forum, and not some dude in Mississippi, says so.
Whether you construct your own or get help from others, suggest that you get someone independent to validate your Excel logbook to ensure that it doesn’t have any errors that could get you into hot water with the FAA. Also, there are a number of free or cheap electronic logbooks available, for example try searching using the string “excel pilot logbook”. I’ve seen some pretty good ones out there that would take you a great deal of time to replicate.
Could you please post the formula for the currency requirements?
I’d be interested as I didn’t know how to get the formula to compute calendar months on a rolling basis.
I could get a formula to look at specific ranges of months in a fixed range of months and get it to accurately calculate the currency, but not get it to automatically look back at from the first of each month. I would have had to change the formula and range of data each month which wasn’t acceptable for me.
Ok, that has potential as long as the above formula pointing to a cell where the date is place contains the today() statement.
By itself, it would require manual input of the month.
The real trick as this alone will give the range of dates to look at, but not look to additional data like counting a number of Y’s in three different columns for approaches, holds and tracking and making sure all three are checked at a rolling 6 month period of time.
I.E One flight just may be an approach, but not a hold, and another flight, one just may go out and knock out a hold. This scenario would not be typical but needs to be factored in as it is a very real possibility. A cross country could meet the tracking needs, but no approach or hold was executed.
My instinct sez an array formula will be needed and that is where my weakness is.