Log book on a spread sheet

[stickandrudderman]

Hi all,
I hope someone can save me from trawling through millions of google results that nearly get me the answer...........


I want to create a spread sheet as a log book but am having what I hope is a simple problem:
In my D column I will write either P1 or P2.
Column J will have the flight duration which is a working formula result of G and H.
Column K will have total time P1
Column L will have total time P2


I want the data in columns K and L to be the result of a simple sum formula that is dependent on what I put in column D (P1 or P2).
That is: if I type "P1" in column D I want the resultant sum of column J to be added cumalatively to column K whereas if I type "P2" then I want the result in column L.


Anyone?

[Keef]

Not sure I've understood, but if you're looking for cell K99 (or whatever) to have the contents of J99 if D99 says P1, then you'd put in K99
=IF(D99="P1",J99,0)
and in L99 you'd have
=IF(D99="P2",J99,0)

You could even have an error-check in a later column to say
-IF(K99+L99<>J99,"Error","")
which would warn you that you'd got a duff entry in D99.

You could go on to have it pick up Pu/t and P1/S and all the other fun ideas. Mine does.

If you want a cumulative hours column, then you would add K99 to whatever was in the cumulative column cell above.

[stickandrudderman]

Thanks for the quick reply.


in K99:
=IF(D99="P1",J99,0)
in L99 you'd have
=IF(D99="P2",J99,0)


But this doesn't seem to make sense as both "IF" result in the same "J99,0"


To explain further:


The J column is the flight duration, eg 1.6 hours.
The K column is P1 total hours
The L column is p2 total hours.


So if I had 100 hours total P1 time and 25 hours total P2 time:
K3 will show 100 and L3 will show 25


Now, I fly P1 for 1.6 hours so I want K4 to show 101.6 and L4 to show 100.


I then fly P2 for 0.5 hours so I want K5 to show 101.6 and L5 to show 100.5


Thanks for looking!


Do I assume that "0" means that nothing will appear in that cell?

[stickandrudderman]

Some results:


To get the "P1" or "P2" to be the determinate factor I ended up with this:


=IF(D4=ʺP1ʺ,K3+J4,)


and in the next column:


=IF(D4=ʺP2ʺ,L2+J4,)


My deduction is that the last comma before the close bracket leaves the cell blank. If I used the "0" as in Keef's example above the I would get "0" in that cell.

[Old Speckled Aircrew]

To make K & J columns cumulative just add the previous value to your if statement.

Leave line 2 blank and in K3:
=IF(D3="P1",J3,0)+K2

and in L3
=IF(D3="P2",J3,0)+L2

Then fill down

[Keef]

Originally Posted by stickandrudderman

Thanks for the quick reply.


in K99:
=IF(D99="P1",J99,0)
in L99 you'd have
=IF(D99="P2",J99,0)


But this doesn't seem to make sense as both "IF" result in the same "J99,0"

No because K99 is looking for "P1" and L99 is looking for "P2". Only one of those two will display a number.
If D99 is "P1" then K99 will display J99. If it isn't "P1" then it will display zero (or not display any value if formatted that way - see below).
Likewise L99 will display J99 only if D99 says "P2".

My deduction is that the last comma before the close bracket leaves the cell blank. If I used the "0" as in Keef's example above the I would get "0" in that cell.

I don't like using the "comma and no value" to blank a zero-value cell. That can do strange things to addition later on. If you want a true blank, then use " " to insert a space. If you want all zeros suppressed, that's best done by a format statement:
Format - Cells - Custom - 0.0 ; (0.0);
which would show positive numbers as 0.0, negatives as (0.0), and zero as blank. The space after 0.0 and before the ; means the positive and negative numbers are aligned.

[stickandrudderman]

Thanks, I think I've got it.

[ExGrunt]

Hi stickandrudderman,

My deduction is that the last comma before the close bracket leaves the cell blank.

The IF function takes three parameters Test, Value if true, Value if false. If you leave it blank after the comma excel will insert a NULL on an evaluation of FALSE which is not the same as blank, which as Keef says can produce unexpected results when doing calculations.

This is a classic 'logic gate' problem, ie you want a value to appear if a check of another cell evaluates to TRUE. ie cell d99 equals P1.

In excel TRUE has the value 1 and FALSE has the value 0. This can then be used as a simple multiplier:

So in K99 enter the formula =(d99="P1")*j99

When d99 is not equal to P1 the brackets evaluate to 0.

I would not use a space as that is a 'text' value and can introduce a different set of errors. If you really want a NULL then do so explicitly by using "" (without a space).

As an aside: How are you dealing with minutes? Summing HH:MM values can cause other 'glitches'.

HTH

EG

[stickandrudderman]

I confess that I had some help to get to this:


=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+0.2,ʺʺ)


where the +0.2 is the extra 12 minutes for taxi time.

[ExGrunt]

I confess that I had some help to get to this:


=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+0.2,ʺʺ)


where the +0.2 is the extra 12 minutes for taxi time.

Potentially, there are all sorts of risks with this formula, but without seeing the rest of the sheet it is difficult to assess what is going on. For example if I13 = 0 then the formula will produce 0.2, which I don't think that is what is expected. Equally, when mixing time and decimal values, 0.2 can be interpreted as 0.2 of a day (because excel stores dates and times as a decimal value) so 0.2 can be 4 hours 48 minutes.

One thing you should avoid is constants in formulas ie +0.2. it is good practice to put the value in a cell with a proper label. Some people like to use a named value. When giving names it is usual to capitalise parts of the name. This means that you can type in the name in lower case eg: taxitime and if Excel recognises it will convert it to the capitalised name eg TaxiTime. It is a quick visual check that the correcct name is being used. It also makes the formula a bit more understandable eg:

=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+TaxiTime,ʺʺ)

HTH

EG

[stickandrudderman]

Thanks.
It appears that I bit off more than I could chew with this particular task and I have recruited a friend who has sorted it for me.