Excel logbook question
Thread Starter
Join Date: Aug 2000
Location: formally Alamo battleground, now the crocodile with palm trees!
Posts: 965
Likes: 0
Received 0 Likes
on
0 Posts
Excel logbook question
Any Excel gifted ppruners that can help me out with the following issue?
I have my logbook in Excel and I am currently flying three different aircraft types, that I like to break in hours flown in each type.
I have a cell that I dedicated "aircraft type". What formula do I need to use if I want to specify e.g. "if aircraft type" cell = E190 than add time in cell "YY" to cell "xx"?
Any suggestions?
7 7 7 7
I have my logbook in Excel and I am currently flying three different aircraft types, that I like to break in hours flown in each type.
I have a cell that I dedicated "aircraft type". What formula do I need to use if I want to specify e.g. "if aircraft type" cell = E190 than add time in cell "YY" to cell "xx"?
Any suggestions?
7 7 7 7
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
7777,
Assume that you have the type in colum B and the corresponding hours in column C and there are 10 rows of data from row 2 - 11. Then you can use the SUMPRODUCT function to calculate the numbers for each type:
Put the type name in cell A12
Put this formula in A13:
=SUMPRODUCT((B2:B11=A12)*C2:C11)
HTH
EG
Assume that you have the type in colum B and the corresponding hours in column C and there are 10 rows of data from row 2 - 11. Then you can use the SUMPRODUCT function to calculate the numbers for each type:
Put the type name in cell A12
Put this formula in A13:
=SUMPRODUCT((B2:B11=A12)*C2:C11)
HTH
EG
If you're looking to analyse the data, note that Excel has various features to help you extract various info, without having to write functions. For example, you can sort the records by aircraft type, then use the Subtotals button to automatically insert subtotals of hours by aircraft type.
More details: Inserting Subtotals in an Excel 2007 Worksheet - For Dummies.
More details: Inserting Subtotals in an Excel 2007 Worksheet - For Dummies.
I use " =SUMIF(range-containing-aircraft-types,cell-containing-aircraft-name, range-containing-flying-hours) ".
My Excel logbook handles that fine for 100ish types. My person preference is to split it over three sheets that contain:
(1) Classical logbook information
(2) Calculations
(3) 1 page presentational logbook summary that I can print out.
(1) I enter data on, (3) is all that useful stuff like total experience, hours on type, hours in last 28/90/365 days, instructional hours, expiry dates....
(2) is just necessary but best kept away from the other two.
G
My Excel logbook handles that fine for 100ish types. My person preference is to split it over three sheets that contain:
(1) Classical logbook information
(2) Calculations
(3) 1 page presentational logbook summary that I can print out.
(1) I enter data on, (3) is all that useful stuff like total experience, hours on type, hours in last 28/90/365 days, instructional hours, expiry dates....
(2) is just necessary but best kept away from the other two.
G
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
Genghis,
I agree that your formula is an equally workable solution and your separation of inputs, workings and outputs is well in line with good excel practice.
As a counsel of perfection have you considered adding a further worksheet of cross check calculations? eg: count of input data matches count of output data, sum of input hours matches sum of output hours.
HTH
EG
I agree that your formula is an equally workable solution and your separation of inputs, workings and outputs is well in line with good excel practice.
As a counsel of perfection have you considered adding a further worksheet of cross check calculations? eg: count of input data matches count of output data, sum of input hours matches sum of output hours.
HTH
EG