Log Book Summary Formuala
Thread Starter
Join Date: Jan 2007
Location: Australia
Posts: 9
Likes: 0
Received 0 Likes
on
0 Posts
Log Book Summary Formuala
I am wanting to personalize my excel logbook and want to add up my totals on type, however, I don't know a formula to do this? 
Can you help?
Can you help?
Its a bit trickier than it looks. You need to use a sumif function.
Have a look at my version, unlock it with the password 'pprune' to see the formulas.
MEGAUPLOAD - The leading online storage and file delivery service
Have a look at my version, unlock it with the password 'pprune' to see the formulas.
MEGAUPLOAD - The leading online storage and file delivery service
Join Date: May 2001
Location: NZ
Posts: 656
Likes: 0
Received 0 Likes
on
0 Posts
For each entry (hidden off to the side of your main logbook screen area)
=if(D4="C172",sum(H4:L4),"")
D4 is reference cell for aircraft type
sum(H4:L4) is the range of columns you want to be added for the flight if the answer is true i.e. C172
"" means blank/zero will be the answer if the answer is false.
Create as many columns with the formula above that you have type ratings.
Highlight all your different rating formulas and drag down to your last avail row to repeat the formulas for each flight entry.
Have a total at the bottom of each column.
Back on your main logbook screen area, under your totals rows for your PIC/DUAL/MULTI etc have a section for your different types, with the time on type next to each by using the simple formula =Y325. where Y325 is the total of column that you created for your C172 rating.
*All cell references are for example purposes only.
Just had a look at Das Uber's spreadsheet, very techy and beyond my excel capability. My info above is a more longwinded, layman affair if you will.
Cheers
S2K
=if(D4="C172",sum(H4:L4),"")
D4 is reference cell for aircraft type
sum(H4:L4) is the range of columns you want to be added for the flight if the answer is true i.e. C172
"" means blank/zero will be the answer if the answer is false.
Create as many columns with the formula above that you have type ratings.
Highlight all your different rating formulas and drag down to your last avail row to repeat the formulas for each flight entry.
Have a total at the bottom of each column.
Back on your main logbook screen area, under your totals rows for your PIC/DUAL/MULTI etc have a section for your different types, with the time on type next to each by using the simple formula =Y325. where Y325 is the total of column that you created for your C172 rating.
*All cell references are for example purposes only.
Just had a look at Das Uber's spreadsheet, very techy and beyond my excel capability. My info above is a more longwinded, layman affair if you will.Cheers
S2K
Last edited by Sqwark2000; 19th Sep 2010 at 10:56.
Moderator
Have a read of the array function which is set up exactly for this sort of thing. Indeed, I have a requirement for aircraft operation type additions with maintenance records and that is exactly analogous to the logbook thing you are trying to do.
Set up the tables as you would do normally, ie including something like
Column 1 ....... Column 2
(rangeA:B)..... (rangeX:Z)
C172N...............2.55
GY180...............0.45
PA18.................3.50
etc
Put the following sort of formula in the cell where you want to do the selective addition and then execute it using CTRL-SHIFT-ENTER rather than the normal ENTER. This tells Excel it is an array variable which you can recognise by the { } squiggly brackets around the whole expression. Also the active cell remains the same after you execute the operation.
{=SUM(IF(rangeA:rangeB="keyword",rangeX:rangeZ,""))}
This might become, say
{=SUM(IF(C5:C457="C172N",G5:G457,""))}
[note that you don't type in the squiggly brackets - Excel puts them in when you execute the expression]
which says to Excel something like ..
"Look down column C between rows 5 and 457, inclusive, and, if the aircraft type for the row in question is C172N, then go over to column G at that row and add the value there to a running total for C172N.
However, if the aircraft type in column C is NOT C172N, ignore that row, go onto the next row and do the exercise again ..."
To save your having to change the range just set up the log with a lot of empty rows which you fill in progressively.
I just set up a sample as above and it works real beaut ... However, I recall the hassles I had getting on top of array operations in Excel so, if you have a problem with this by all means send me a copy of part of your spreadsheet and I'll set it up for you.
(Ignore the dot leaders - I've forgotten how to set up columns in PPRuNe so the dots are the next best thing I can find to make it look like columns)
Set up the tables as you would do normally, ie including something like
Column 1 ....... Column 2
(rangeA:B)..... (rangeX:Z)
C172N...............2.55
GY180...............0.45
PA18.................3.50
etc
Put the following sort of formula in the cell where you want to do the selective addition and then execute it using CTRL-SHIFT-ENTER rather than the normal ENTER. This tells Excel it is an array variable which you can recognise by the { } squiggly brackets around the whole expression. Also the active cell remains the same after you execute the operation.
{=SUM(IF(rangeA:rangeB="keyword",rangeX:rangeZ,""))}
This might become, say
{=SUM(IF(C5:C457="C172N",G5:G457,""))}
[note that you don't type in the squiggly brackets - Excel puts them in when you execute the expression]
which says to Excel something like ..
"Look down column C between rows 5 and 457, inclusive, and, if the aircraft type for the row in question is C172N, then go over to column G at that row and add the value there to a running total for C172N.
However, if the aircraft type in column C is NOT C172N, ignore that row, go onto the next row and do the exercise again ..."
To save your having to change the range just set up the log with a lot of empty rows which you fill in progressively.
I just set up a sample as above and it works real beaut ... However, I recall the hassles I had getting on top of array operations in Excel so, if you have a problem with this by all means send me a copy of part of your spreadsheet and I'll set it up for you.
(Ignore the dot leaders - I've forgotten how to set up columns in PPRuNe so the dots are the next best thing I can find to make it look like columns)
I think the issue with that is when he gets a few pages in his log book. Having enough iterations of the cycle intensive array formula is going to end up bogging his workbook down almost to a stop.
Post your logbook and a few of us will probably take a look at it for you. Remove your precious identity data if you need to.
Post your logbook and a few of us will probably take a look at it for you. Remove your precious identity data if you need to.
Join Date: Apr 2008
Location: Australia
Posts: 669
Likes: 0
Received 0 Likes
on
0 Posts
Having enough iterations of the cycle intensive array formula is going to end up bogging his workbook down almost to a stop.
By nature, a log book is much more like a database (not a spreadsheet).
With considerable banging and discomfort you can get Excel to the job, of course, but increasingly, you will come to realise that you trying to insert a square peg in a round hole.
You should be using a database, like Microsoft Access.
Moderator
Having enough iterations of the cycle intensive array formula is going to end up bogging his workbook down almost to a stop.
Probably not the case. Unless the thing is running on a dinosaurian legacy machine, the speed will be fine.
The spreadsheet I knocked up for my maintenance records is about 4MB, has in the vicinity of 16,000 line records so far (amongst a bunch of other stuff) spread amongst a number of aircraft and runs something like several hundred automatic array calculations .. it runs without any problem on a four year old laptop.
I can't see the OP having any problems using Excel to run a comparatively tiny log book database setup.
Excel is actually the WRONG tool for this job
Of course it is - no argument there. (Probably more a case of not being the better tool ?). Actually, having thought about it a bit more, a logbook is exactly like a spreadsheet so I don't think it matters which program you use .. preferably whichever you find easier to set up ?
Just like my example where a simple spreadsheet was set up to do a few little jobs and then just grew and I have ever since regretted not taking the time then to transfer it to something more appropriate. One of these days I'll get around to transferring it to a RDB program, whether Access or one of the more capable examples.
For the OP, he most likely doesn't have Access and, for other than really big databases, Excel works fine if it's set up sensibly and is very simple to drive.
In any case, Access is not much more than a fancy, tweaked version of Excel. What, pray tell, is the underlying difference between a RDB program setup, with multiple defacto spreadsheets for it to drive .. and a spreadsheet or spreadsheet workbook ?
Not suggesting that one should prefer Excel for RDB work but it is a very capable program for any sort of engineering and scientific work. The thing must have in excess of 400 or 500 built in mathematical functions and other capabilities ..
For his/her simple logbook requirements, Excel will be just fine .. and comes along with Office .. why buy Access for simple stuff when one doesn't need a sledgehammer to crack a walnut ?
... (stands by for incoming from the diehard MicroSoft folk ..)
Probably not the case. Unless the thing is running on a dinosaurian legacy machine, the speed will be fine.
The spreadsheet I knocked up for my maintenance records is about 4MB, has in the vicinity of 16,000 line records so far (amongst a bunch of other stuff) spread amongst a number of aircraft and runs something like several hundred automatic array calculations .. it runs without any problem on a four year old laptop.
I can't see the OP having any problems using Excel to run a comparatively tiny log book database setup.
Excel is actually the WRONG tool for this job
Of course it is - no argument there. (Probably more a case of not being the better tool ?). Actually, having thought about it a bit more, a logbook is exactly like a spreadsheet so I don't think it matters which program you use .. preferably whichever you find easier to set up ?
Just like my example where a simple spreadsheet was set up to do a few little jobs and then just grew and I have ever since regretted not taking the time then to transfer it to something more appropriate. One of these days I'll get around to transferring it to a RDB program, whether Access or one of the more capable examples.
For the OP, he most likely doesn't have Access and, for other than really big databases, Excel works fine if it's set up sensibly and is very simple to drive.
In any case, Access is not much more than a fancy, tweaked version of Excel. What, pray tell, is the underlying difference between a RDB program setup, with multiple defacto spreadsheets for it to drive .. and a spreadsheet or spreadsheet workbook ?
Not suggesting that one should prefer Excel for RDB work but it is a very capable program for any sort of engineering and scientific work. The thing must have in excess of 400 or 500 built in mathematical functions and other capabilities ..
For his/her simple logbook requirements, Excel will be just fine .. and comes along with Office .. why buy Access for simple stuff when one doesn't need a sledgehammer to crack a walnut ?
... (stands by for incoming from the diehard MicroSoft folk ..)
In any case, Access is not much more than a fancy, tweaked version of Excel. What, pray tell, is the underlying difference between a RDB program setup, with multiple defacto spreadsheets for it to drive .. and a spreadsheet or spreadsheet workbook ?
Apart from that, yer, totally the same.
On topic, having a new worksheet aka "tab" for every page in your logbook is not the way to go. The issues I can see are;
1. Its going to be difficult to navigate once you get enough pages. Youre only able to click forwards or backwards either 1 sheet at a time, or to the start/finish of your sheets. Hence, if you have 3 logbooks and 300 odd worksheets, its going to take you 'a while' to get to sheet X without VBA knowledge. Excel is not designed for this.
2. Memory, unless you're using a 64 bit version (with the memory to match), excel only allocates a certain amount of memory depending on version. Exceed that and you're in trouble, which is not actually impossible to do once you have a few logbooks. An excel sheet only 4 mb in size can actually occupy 40 meg of memory allocation when trying to save. In addition to the memory leaks and other memory limits like heap space you're also going to run into track cell memory problems with the unused cells.
eg, if you have 300 workbooks (only 3 logbooks, maybe even less, how many pages in a normal logbook anyway?), at 17,179,869,184 cells per workbook, well.... It'll work for a while.
Its why I have used a single workbook for the logbook section of my little sheet. If you needed a page per page listing of your totals for some reason, then create a function that lists the totals every X lines of the main worksheet and displays them in a seperate table or workbook. Same advantages of having 3,653,523,652 worksheets, no disadvantage of having a memory hungry, impossible to navigate excel sheet.
Or you could just use mine, either way.
The pivot table is going to be difficult to implement on possibly hundreds of seperate worksheets, and will be very labor intensive. I don't recommend it without VBA knowledge.
Moderator
Excel is flat file, has a record limit, is unable to create data relationships, has no referential integrity, is single user is has a completely different memory management mechanism when dealing with multi workbook projects.
Not suggesting that Excel is preferable to Access for RDB work in general. However, for simple tasks it does a fine job and just about everyone has a copy. It's a bit like saying you must have a B747 to fly around the country when you can probably make do just fine in a C310 for mum, dad, and the two kids.
My example (which certainly should be, and eventually will be, in Access or something similar) has 8 main workbooks and 2000+ line item entry sets per each of those workbooks and works fine in Excel for my purposes although there are a few minor inconveniences - a simple logbook will work fine in Excel even if it might work much finer in Access.
having a new worksheet aka "tab" for every page in your logbook is not the way to go
Why ever would one do that ?
Not trying to be difficult and certainly acknowledging that a RDBMS will do the job better all round .. but if one just wants something simple like a personal logbook on the PC one wouldn't rush out and buy Access just for that purpose, surely ?
Perhaps we should simply agree to disagree ?
Not suggesting that Excel is preferable to Access for RDB work in general. However, for simple tasks it does a fine job and just about everyone has a copy. It's a bit like saying you must have a B747 to fly around the country when you can probably make do just fine in a C310 for mum, dad, and the two kids.
My example (which certainly should be, and eventually will be, in Access or something similar) has 8 main workbooks and 2000+ line item entry sets per each of those workbooks and works fine in Excel for my purposes although there are a few minor inconveniences - a simple logbook will work fine in Excel even if it might work much finer in Access.
having a new worksheet aka "tab" for every page in your logbook is not the way to go
Why ever would one do that ?
Not trying to be difficult and certainly acknowledging that a RDBMS will do the job better all round .. but if one just wants something simple like a personal logbook on the PC one wouldn't rush out and buy Access just for that purpose, surely ?
Perhaps we should simply agree to disagree ?
No no, I'm on the same page as you. I thought you meant excel and access were almost identical on an architecture level, which they are not.
For basic stuff you are right in what you say, in my opinion.
As for having a workbook per page of your logbook, you would be surprised. Over the years here I have seen many people offer their excel logbooks in this format. Ive always thought it amore or less stupid implementation but some people will go to extensive lengths to make the electronic version match the paper version. To be clear, im not insinuating that you suggested this was a good way to go about things.
The thread starter mentioned that his data is on multiple worksheets so lets hope he isn't going about things in this way.
Ive been offering my free excel logbook for a while now. Ive got a bit of free time coming up so I might sit down and code out a solid access solution and offer it free also, sometime in the not too distant future. Be interesting to see what kind of added capability could be offered by going that route over excel.
For basic stuff you are right in what you say, in my opinion.
As for having a workbook per page of your logbook, you would be surprised. Over the years here I have seen many people offer their excel logbooks in this format. Ive always thought it amore or less stupid implementation but some people will go to extensive lengths to make the electronic version match the paper version. To be clear, im not insinuating that you suggested this was a good way to go about things.
The thread starter mentioned that his data is on multiple worksheets so lets hope he isn't going about things in this way.
Ive been offering my free excel logbook for a while now. Ive got a bit of free time coming up so I might sit down and code out a solid access solution and offer it free also, sometime in the not too distant future. Be interesting to see what kind of added capability could be offered by going that route over excel.
Moderator
I thought you meant excel and access were almost identical on an architecture level
No argument there. In an ideal world we would always use the best tool for the job at hand. Unfortunately, the bean counters always have a meddling view ... and we end-chaps have to figure work arounds to get the job done .. while, at the same time, keeping ourselves nice for the Regulators and auditors. Did I mention that I'm looking forward to Christmas and a couple of weeks where we close the doors ?
As for having a workbook per page of your logbook, you would be surprised. Over the years here I have seen many people offer their excel logbooks in this format.
amazing how folks can be blinkered and try to map one system's protocols across to another instead of following the "horses for courses" thing.
Ive been offering my free excel logbook for a while now.
Tried to download and have a looksee but the system wouldn't co-operate last night. Will try again and see how I go.
Be interesting to see what kind of added capability could be offered by going that route over excel.
I've no doubt that an Access implementation will be more elegant that Excel .. however, one of the problems of being an engineer with a lot of GA design background is that one has been brought up on the "you want to spend a dollar on it ? why can't you find a way to do it for 10c ?" side of the tracks ...
Interestingly, one sees many very simple and low cost solutions which make far more sense than just throwing more money at problems. Some which spring to mind include a few of John B's tricks with the Victa program in long ago days, Neddy N's approach to some of the AirVan testing and our own use of simple long lense video gear to do runway width test work when others were intent on throwing big bucks and fancy electronics at the problem .. to get about the same functional end accuracy.
We should catch up for an ale some time ...
No argument there. In an ideal world we would always use the best tool for the job at hand. Unfortunately, the bean counters always have a meddling view ... and we end-chaps have to figure work arounds to get the job done .. while, at the same time, keeping ourselves nice for the Regulators and auditors. Did I mention that I'm looking forward to Christmas and a couple of weeks where we close the doors ?
As for having a workbook per page of your logbook, you would be surprised. Over the years here I have seen many people offer their excel logbooks in this format.
amazing how folks can be blinkered and try to map one system's protocols across to another instead of following the "horses for courses" thing.
Ive been offering my free excel logbook for a while now.
Tried to download and have a looksee but the system wouldn't co-operate last night. Will try again and see how I go.
Be interesting to see what kind of added capability could be offered by going that route over excel.
I've no doubt that an Access implementation will be more elegant that Excel .. however, one of the problems of being an engineer with a lot of GA design background is that one has been brought up on the "you want to spend a dollar on it ? why can't you find a way to do it for 10c ?" side of the tracks ...
Interestingly, one sees many very simple and low cost solutions which make far more sense than just throwing more money at problems. Some which spring to mind include a few of John B's tricks with the Victa program in long ago days, Neddy N's approach to some of the AirVan testing and our own use of simple long lense video gear to do runway width test work when others were intent on throwing big bucks and fancy electronics at the problem .. to get about the same functional end accuracy.
We should catch up for an ale some time ...
haha Johh. As a sausage factor instructor and hence an idiot by PPruNE's very own community definition, I doubt you'd get much interesting conversation out of me! Sounds like you spend alot of time with some very interesting challenges at work however.
DAHMAH: Dont forget, if youd like to upload your sheet, ill take a look at it for you.
DAHMAH: Dont forget, if youd like to upload your sheet, ill take a look at it for you.
Moderator
As a sausage factor instructor and hence an idiot by PPRuNe's very own community definition
Love that one ... copyrighted or can anyone use it ?
some very interesting challenges at work
has its moments, I guess. I keep telling them that I'm there until I get bored and then I'm off sailing ..
Love that one ... copyrighted or can anyone use it ?
some very interesting challenges at work
has its moments, I guess. I keep telling them that I'm there until I get bored and then I'm off sailing ..
