+ Reply to Thread
Results 1 to 4 of 4

Hourly Daily Monthly Energy Usage charts

  1. #1
    Registered User
    Join Date
    02-16-2018
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    2

    Hourly Daily Monthly Energy Usage charts

    Gurus
    I have an energy monitor which logs in to Google Spreadsheets and would like to chart Hourly, Daily and Monthly average Energy usage.
    I am able to calculate the Monthly average using a formula AVERAGEIFS(B46394:B51316,A46394:A51316,">="&DATE(2018,1,1),A46394:A51316,"<="&DATE(2018,1,31)) for the month of January. This one needs to be hard coded and not automatic or dynamic, I struggled with it and never could generate Daily or hourly averages due to hardcoding the formulas and generating charts as the purpose is not served.

    Any idea how I can do this? Thanks in advance.
    Kumar
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,143

    Re: Hourly Daily Monthly Energy Usage charts

    Make yourself a "mini-pseudo-database" and use the excel database functions - 'DAVERAGE', 'DSUM', etc.
    E.g. in this file below, after opening it, run the macro 'restore_formulas' or enter the functions manually from the 'Description' sheet.
    Look at the functions.
    For example: in 'A2', 'D2' with reference to 'J4', 'J5', 'K4', 'K5' with reference to 'E2' ('E4'), 'F2' ('F4').
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-16-2018
    Location
    USA
    MS-Off Ver
    MS Office 2016
    Posts
    2

    Re: Hourly Daily Monthly Energy Usage charts

    Thanks much for your response. For some strange reason the macros did not work in excel, it gives me a Div/0 error. However copy and paste works on Google Sheets (that's what matters!).
    I am able to get average for February of 2017, but the sum also is same number, somethings wrong.
    Also how do I get average values for March -Dec 2017 (2018)? Is it that I can only do one average per month? How about the Daily average? I will use these values to chart for a visual indicator.
    Sorry for being late.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: Hourly Daily Monthly Energy Usage charts

    Hello 1kumar and Welcome to Excel Forum.
    Perhaps this will be of some help.
    Lists are set up in columns J:L,
    Data validation referencing those lists is applied to the cells that are highlighted in green,
    Array entered formulas* referencing the green cells are set up in the cells that are highlighted in blue,
    The formulas for hourly average are similar to: =AVERAGE(IF((HOUR(A2:A1956)=HOUR(F2))*(B2:B1956<>""),B2:B1956))
    The formulas for daily average are similar to: =AVERAGE(IF((INT(A2:A1956)=G2)*(B2:B1956<>""),B2:B1956))
    The formulas for monthly average are similar to: =AVERAGE(IF((MONTH(A2:A1956)=H2)*(B2:B1956<>""),B2:B1956))
    The formulas for average of a range a similar to: =AVERAGE(IF((INT(A2:A1956)>=F6)*(INT(A2:A1956)<=H6)*(B2:B1956<>""),B2:B1956))
    Note: This assumes that you do not want blanks averaged as zeros
    *Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 03-29-2016, 06:15 PM
  2. [SOLVED] Formula to Group analytical data imputed daily into daily weekly and monthly analysis.
    By tianasamour in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-13-2015, 06:10 PM
  3. Replies: 1
    Last Post: 04-13-2015, 04:38 PM
  4. Replies: 3
    Last Post: 08-20-2014, 12:19 PM
  5. Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  6. Calculate daily targets given: month, monthly budget, daily indexes
    By Chuckyrp in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-06-2013, 03:30 AM
  7. Replies: 1
    Last Post: 07-11-2008, 05:57 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1