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? |
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. |
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? |
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 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 |
Originally Posted by stickandrudderman
(Post 8646164)
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" 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. |
Thanks, I think I've got it.
|
Hi stickandrudderman,
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 |
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. |
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. 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 |
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. |
All times are GMT. The time now is 19:34. |
Copyright © 2024 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.