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

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

Or try this

=COUNTIF(B:B,"=Type")

You can extend it to multiple counts in one cell e.g.

=COUNTIF(B:B,"=Type") + COUNTIF(C:C,"=Othertext")

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.

http://media.wiley.com/Lux/92/72492.image0.jpg

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

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

No, but that's a good idea. A task for a long winter evening.

G