Log book on a spread sheet
Thread Starter
Join Date: Aug 2006
Location: Sth Bucks UK
Age: 60
Posts: 927
Likes: 0
Received 0 Likes
on
0 Posts
Log book on a spread sheet
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?
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?
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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.
=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.
Thread Starter
Join Date: Aug 2006
Location: Sth Bucks UK
Age: 60
Posts: 927
Likes: 0
Received 0 Likes
on
0 Posts
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?
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?
Last edited by stickandrudderman; 8th Sep 2014 at 11:43.
Thread Starter
Join Date: Aug 2006
Location: Sth Bucks UK
Age: 60
Posts: 927
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Join Date: Jan 2006
Location: Gloucestershire, England
Posts: 40
Received 0 Likes
on
0 Posts
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
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
Official PPRuNe Chaplain
Join Date: Apr 2001
Location: Witnesham, Suffolk
Age: 80
Posts: 3,498
Likes: 0
Received 0 Likes
on
0 Posts
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.
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.
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
Hi stickandrudderman,
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
My deduction is that the last comma before the close bracket leaves the cell blank.
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
Last edited by ExGrunt; 9th Sep 2014 at 15:21.
Thread Starter
Join Date: Aug 2006
Location: Sth Bucks UK
Age: 60
Posts: 927
Likes: 0
Received 0 Likes
on
0 Posts
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.
=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+0.2,ʺʺ)
where the +0.2 is the extra 12 minutes for taxi time.
Join Date: Mar 2003
Location: England
Posts: 286
Likes: 0
Received 0 Likes
on
0 Posts
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.
=if(I13<>ʺʺ,HOUR(I13)+ROUND(MINUTE(I13)/60,1)+0.2,ʺʺ)
where the +0.2 is the extra 12 minutes for taxi time.
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