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.
Thanks
What are you trying to calculate? If you have a formula that isn’t working the way you think it should, post it here.
I have my log book “exported” to an Excel file, so I have actual data to mess with to see if I can help any.
Post in this thread so others can learn from other’s input. 
Allen
I’m expert when it comes to Excel. Either post your questions here or send me a private message.
send me an email address and I’ll email it do you
I can email you the file that I’m working with as well
I’m trying to make a formula to calculate if I am instruemnt current.
something like this
Instrument approaches last 6 months: 8
Instrument Current: Yes (In Green) or No (in red)
this is the formula I have to calculate instrument approaches in 180 days.
=SUM(IF((FlightLog!A4:Totals!A1112>=(TODAY()-180)),FlightLog!O4:O1112))
Then this is the formula to tell me if I’m instrument current
=IF(E33>=6, “YES”, “NO”)
You might need to look at the spreadsheet I have in order for it to make sence.
I also need help with the formulas for: Cross Country Instruction received, Cross country Solo, Cross Country PIC, Night Instruction Received Night PIC , Night SIC and Cross Country SIC.
Hope this helps
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.
Looking at the spreadsheet will help so please email it to me at damiross@gmail.com
Something told me in the background this would be a IA currency question 
For your second formula, this is easy enough for me. I will talk in thoughts for your first formula after resolving your second formula question.
=IF(A3>=6, “current”,“not current”), this worked for me, but I did not have it referring to another sheet as indicated in your example.
I placed it in the data worksheet, where as your formula looks like it was on a different sheet (flight log).
If you want the formula to look back to a data sheet, then use the following.
=IF(Sheet1!A4>=6, “current”,“not current”)
(change sheet1! to your Flightlog sheet name and **change the cell references **to your value total of instrument approaches.
If you have 5, it will dsiplay not current. If you have 6 or greater, it will show current.
To really get fancy as you indicated the want for color coding, use conditional formatting of the cell to make current green and not current red.
For your first formula regarding the actual currency computation, I think there are a couple of ways to tackle that one.
One is the lookup, which is a pain in the rear end.
Second is to use the Array function of the formula, where you use the Control, Shift and Enter to enter the formula in the cell.
office.microsoft.com/en-us/excel … 01033.aspx
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.
Hope what I provided helps!
Allen
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.
Do you know the currency requirements for instrument approaches? Quite a few pilots have problems with it when determining IA currency requirements.
There is a difference between 180 days and six calendar months, and 6 approaches in the last 180 days as proposed in the formula does not necessarily make you current on your iA rating.
Unless you know currency requirements intimately, all the knowledge of Excel won’t mean a hill of beans unless of course you find GIGO acceptable. I myself do not…
I know my limits, do you?
Allen
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.
Allen
Yes, to both questions (or is that 4 questions, with the first one counting as 3 questions because of the 3 question marks).
FYI: I not only read the appropriate FAR but then I outlined it to verify what I read.
In order to do the spreadsheet properly, I need to see the whole spreadsheet in context. I did offer the caveat of knowing I needed to look at the whole spreadsheet.
With a good ole fashioned COCK-OFF, BTLS style.
Lets all meet in LAS and Dami and Allen can whip out thier UNITS and then we will know who is the KING of the FN hill.
You two alpha aholes are pathetic. Get a collective life!
My appologies for any off color words, but ENOUGH ALREADY.
Don’t read my posts if you don’t like what I have to say.
Please make any further comments to the banter thead and leave this on topic for electronic log books.
Thank you.
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.
magic1872-
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.
I built my own logbook after a paper logbook you would buy. Contains the same information as a real one. I do keep a paper logbook as well. I got all the formulas to work thanks for the help!!
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.
Thanks!
Allen
=EOMONTH(date,number of months)+1
End of month function can be used to determine the 1st day of the month.
Enter the date, months (plus or minus) and then add 1
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.
Allen