Calculating imported Data Text Files

  • I import a log of my internet usage times for a month, using ‘Data – Get external data – Import text file’. The data is start and end times, each range seperated by two blank cells, e.g. data is contained in B5, B6, B9, B10, B13, B14 et seq. C6, C10, C14 calculate the total time (B6-B5+if(B6<B5,1). How can I copy the formulae in C6 to C10 and every subsequent 4th cell down to C882, apart from the laborious individual pastes ? One answer would be to get the data to display on two rows, start times on row B, end times on row C. The formula on row C being copied the length of the row by dragging. The Import text wizard automatically places the data in a column.
    MS Office 2000

  • Hi,


    Insert one column anywhere you like, say col.C, and put


    in cell C6
    =IF(MOD(ROW(A1),4)=1,B6-B5,"")


    then drag down as you want



    Second problem


    In Cell B5
    =IF(AND(B5<>"",B6<>""),B6,"")
    In cell B6
    =IF(AND(B5<>"",B6<>""),B6-B5,"")


    then drag down both cells


    hope this works

  • Calculating imported Data Text Files


    Well, in Jindon’s first solution I modified it to
    =IF(MOD(ROW(A1),4)=1,B6-B5+IF(B6<B5,1))
    The inclusion of “IF” caters for times going through Midnight.
    However it returns “False” where there is no valid calculation (B7-B6).
    In Domenic’s solution it does not return any “False” but, in both solutions the last result is at C194, C196 et seq display the #value! error. The total hours up to C194 is 12:29:02. The next entry only adds 1:54:35 so why it should not display this I do not know. Also tried manually entering the formula in C194 and dragging down but still got the #value! error (against the “Value if true” equation). Grateful for any further ideas.
    Thank you

  • Calculating imported Data Text Files


    Hi jindon


    Your parantheses do not match - 5 open 6 closing. Corrected but still get false instead of a blank cell and the paste stops at C194. The main problem now is being unable to drag past C194 without getting #value!

  • Hi, Garcia


    If that a case, data may be "TEXT".


    can you check inserting following formula


    =istext(b194)


    it it returns TRUE then it is text, therefore cannot be calculated.
    I mean it must be converted to the form to be able to calculated.
    Regards,
    Jindon

  • Quote


    Jindon


    I would not have believed this. b198 is True (Text). b194 is False. Yet b198 was copied from the same scource as b194 and is formatted as custom type h:mm:ss
    No matter what I do it remains as Text. Any ideas on this ?

  • Hi,


    1) Enter the number 1 in an empty cell
    2) Select that cell and choose Edit > Copy
    3) Select your data
    4) Edit > Paste Special > Multiply
    5) Click Ok


    Hope this helps!

  • Solved ! When I imported the data, the wizard shows a preview. I told the wizard to import the date column but not to import the Year column (2004,[space]) followed by H:m:s. The date column does not display 1 as 01 so, when 10 July appears it places the comma and space after 2004 into the H:m:s column thus entering the data as Text. Solved by formatting the date column in Excel to show 2 numerals (01)

  • Calculating imported Data Text Files


    Hi
    Presume this is how to do it. Two attachments.
    1. Rla01640 is the raw log, save it.
    2. Internet Usage.xls is my completed sheet.
    Format column A as date, your choice. Format columns B & C as Custom – Type – [h]:mm:ss Enter your formula in C6 – drag down to Row 950. Position cursor at A5.
    Clik Data - Get external data – Import textfile. Choose rla01640.txt. Wizard appears. Select “Fixed width” – Start import at row 2 – Clik Next.
    Delete the 1st and 3rd columns. Move the third column right one point (at 21).(This eliminates the comma).
    Clik – Next. Choose “Do not import” for the 1st column and the 3rd (2004,), clik – Finish. “Existing worksheet” with A5 selected should show. Clik – OK.
    My poor eyesight failed to spot the comma followed by a space in column B.
    Thanks a lot. Le saluda con fraternal afecto en el Señor.
    Garcia. Monk of Solius

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!