+ Reply to Thread
Results 1 to 11 of 11

Need Of a formula that extracts certain data and adds other cells

  1. 11-12-2012, 01:38 AM #1
    Waynej4t
    Waynej4t is offline
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Need Of a formula that extracts certain data and adds other cells

    I am making a excel program that works out my power usage, solar production and cost of bill etc as I am new to excel would appreciate any help

    SHEET ( Power Bill) In Column A, B, C I have days, date, Time which is broken down from Column D which has day-date-time the days are broken up into 5 minutes interval for each day

    What I am aiming to do is create 2 graphs on SHEET Day-Month Graphs & I am not sure if I need to create another worksheet first with extracted data & what formulas would achieve my goal

    ( Graph1) I want to call it Hourly.... For the hourly Graph I am hoping that I can type a date in a cell & excel will look down the column either column B or D and extract all cells relating to that date then I need for it then add all 5 minutes intervals up for each hour of the day ......... Graph will contain the read out of column I & K broken into hours per 24 hours

    ( Graph1) I want to call it Daily.... For the Daily Graph I am hoping that I can type a range of 2 dates in a cell & excel will look down the column either column B or D and extract all cells relating to that dates then I need for it then add all 5 minutes intervals up for each day between those date ......... Graph will contain the read out of column I & K broken into days

    I know how to create Graphs but I don't know how to extract & collate the information

    I have attach the latest version of my Program & again I will appreciate any help & please just bare with me


    Updated Version Power Bill.xlsx
    Last edited by Waynej4t; 11-12-2012 at 02:33 AM.
    Reply With QuoteRegister To Reply
  2. 11-12-2012, 05:25 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Need Of a formula that extracts certain data and adds other cells

    I would suggest you look into using Pivot Tables and Pivot Charts
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 11-12-2012, 05:33 AM #3
    Waynej4t
    Waynej4t is offline
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Need Of a formula that extracts certain data and adds other cells

    Sorry I do not understand them, I thought that I did do a table?
    Reply With QuoteRegister To Reply
  4. 11-12-2012, 05:40 AM #4
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: Need Of a formula that extracts certain data and adds other cells

    You have indeed made a table out of your data, which is not the same as a Pivot Table which is used to condense/analyze data.
    Perhaps have a look at XL help or one of the many tutorials on the subject
    Reply With QuoteRegister To Reply
  5. 11-12-2012, 05:42 AM #5
    Waynej4t
    Waynej4t is offline
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Need Of a formula that extracts certain data and adds other cells

    Ok thank you
    Reply With QuoteRegister To Reply
  6. 11-12-2012, 06:12 AM #6
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Need Of a formula that extracts certain data and adds other cells

    Do you mean something like this:
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 11-12-2012, 06:23 AM #7
    Waynej4t
    Waynej4t is offline
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Need Of a formula that extracts certain data and adds other cells

    Yes that is similar what I am looking for, But how do I set the month or day I am wanting to compare? and can I change the read out to show column I and K??
    And can I covert your days into English from your language ?
    Last edited by Waynej4t; 11-12-2012 at 06:34 AM.
    Reply With QuoteRegister To Reply
  8. 11-12-2012, 06:32 AM #8
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Need Of a formula that extracts certain data and adds other cells

    Right click on Row Labels (Column A) of the table.
    Choose Group and you will see Grouping by Hours (or daily).
    So now choose multiple grouping (for example Monthly, yearly and so on).

    To change column you want SUM in the right box where you see SUM values pull from above columns you want to SUM (I and K columns).
    Reply With QuoteRegister To Reply
  9. 11-12-2012, 06:54 AM #9
    Waynej4t
    Waynej4t is offline
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Need Of a formula that extracts certain data and adds other cells

    Thank You, I have worked out how to change the day year etc but I will have to play around to understand about the To change column you want SUM in the right box where you see SUM values pull from above columns you want to SUM (I and K columns). but thanks I will mark it solve unless I can not work it out I will change the status again

    Just one question is there a way to change the days and hours into English?
    Reply With QuoteRegister To Reply
  10. 11-12-2012, 06:59 AM #10
    zbor
    zbor is offline
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,601

    Re: Need Of a formula that extracts certain data and adds other cells

    Look here:

    PivotTable_Formatted.jpg
    To change SUM field drag from "Choose fields to add to reports" into lower right box (Sigma Values).

    To change days and hourse into english best would be to start your own Pivot table.

    Just delete those sheets and go to your original data, select columns from A to K (last one) and Insert -> Pivot table

    Then (back to picture above) insert Row labels nad Sigma Values again, and group data as you already learn.
    Reply With QuoteRegister To Reply
  11. 11-12-2012, 07:21 AM #11
    Waynej4t
    Waynej4t is offline
    Registered User
    Join Date
    11-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Need Of a formula that extracts certain data and adds other cells

    Thank you I appreciate all your help and time
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1