+ Reply to Thread
Results 1 to 11 of 11

Calculate electric costs

Hybrid View

  1. 05-22-2012, 07:27 AM #1
    Creature
    Creature is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    5

    Calculate electric costs

    Hi all,

    I just registered here. I've got a problem, I've tried Google, but found nothing usefull.

    Well here's my problem. I'm trying to calcute the electric costs with the conditions that the day price is € 0,10 per kWh (07:00-23:00) and night is €0,06 per kWh (23:00-07:00). The weekend (Friday 23:00-Mo 07:00) is also night price.

    I have excel sheet with the following columns, A in format dd-mm-jj uu:mm, B MJ(Mega Joule), C kWh, D Costs/h. The data I get in column B are in hours, in MJ. So I divide that with 3,6 and I got kWh. Just for the info, we, europeans, work with comma as decimal, not with dot.

    Can someone help me?

    Thanks in advance
    Attached Files Attached Files
    Last edited by Creature; 05-22-2012 at 07:57 AM. Reason: Add an attachment as an example
    Reply With QuoteRegister To Reply
  2. 05-22-2012, 07:36 AM #2
    npamcpp
    npamcpp is offline
    Valued Forum Contributor
    Join Date
    04-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    547

    Re: Calculate electric costs

    If you post a workbook, formulas, formats and comma/dot as decimal will be shown correctly to whoever opens the file. Posting a workbook will be much better than describing it.
    Also, "Earth" is not a good description of your location. That piece of information will help us identify your regional settings, so when posting a formula in a post instead of a workbook, we can make sure to use the correct list delimiter.
    Reply With QuoteRegister To Reply
  3. 05-22-2012, 07:59 AM #3
    Creature
    Creature is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate electric costs

    Thanks for the quick replay, npamcpp. I just added an attachment to my first post. Hope you or others can help, thanks in advance.
    Reply With QuoteRegister To Reply
  4. 05-22-2012, 09:10 AM #4
    jimcot
    jimcot is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate electric costs

    Try the following formula in column D:
    =IF(OR(WEEKDAY(A2)=1;WEEKDAY(A2)=7);C2*$H$4;IF(AND(HOUR(A2)>=7;HOUR(A2)<=23);C2*$H$3;C2*$H$4))
    and be sure to check your regional setting and adjust accordingly the WEEKDAY values in the above formula as follows:
    SUNDAY=1 to SATURDAY=7

    JiMcOt
    Reply With QuoteRegister To Reply
  5. 05-22-2012, 09:12 AM #5
    jimcot
    jimcot is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    6

    Lightbulb Re: Calculate electric costs

    Try the following formula in column D:
    ==IF(OR(WEEKDAY(A2)=1;WEEKDAY(A2)=7);C2*$H$4;IF(AND(HOUR(A2)>=0;HOUR(A2)<=7);C2*$H$4;C2*$H$3))
    and be sure to check your regional setting and adjust accordingly the WEEKDAY values in the above formula as follows:
    SUNDAY=1 to SATURDAY=7

    Last edited by jimcot; 05-22-2012 at 09:42 AM. Reason: miss copied
    Reply With QuoteRegister To Reply
  6. 05-22-2012, 09:34 AM #6
    Creature
    Creature is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate electric costs

    Thanks for the reply.

    I implemented the formula in cell D2 and get an error: Invalid name "#Name?". Which cell did you put the formula in? Secondly, where can I see the regional setting for the weekdays? Here in the Netherlands, we see Monday as 1, I think. Not sure though.


    I'm going to look close at formula to understand it.
    Reply With QuoteRegister To Reply
  7. 05-22-2012, 09:41 AM #7
    npamcpp
    npamcpp is offline
    Valued Forum Contributor
    Join Date
    04-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    547

    Re: Calculate electric costs

    be sure to check your regional setting and adjust accordingly the WEEKDAY values in the above formula as follows:
    SUNDAY=1 to SATURDAY=7
    Surely not. You cannot ask people to change their regional settings to accommodate a formula you create, just because you don't know the parameters.

    Weekday takes a second parameter that defines several different aspects of when a week starts.

    From the Office 2010 help files:

    
RETURN_TYPE	NUMBER RETURNED
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Sunday).
11	Numbers 1 (Monday) through 7 (Sunday).
12	Numbers 1 (Tuesday) through 7 (Monday).
13	Numbers 1 (Wednesday) through 7 (Tuesday).
14	Numbers 1 (Thursday) through 7 (Wednesday).
15	Numbers 1 (Friday) through 7 (Thursday).
16	Numbers 1 (Saturday) through 7 (Friday).
17	Numbers 1 (Sunday) through 7 (Saturday).
    http://office.microsoft.com/en-us/ex...010343015.aspx
    Reply With QuoteRegister To Reply
  8. 05-22-2012, 09:54 AM #8
    jimcot
    jimcot is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate electric costs

    npamcpp you are so very accurate. The weekday works irrelevant of the regional settings, please excuse my rashness, as for the formula the correct form is:
    =IF(OR(WEEKDAY(A2)=1;WEEKDAY(A2)=7);C2*$H$4;IF(AND(HOUR(A2)>=0;HOUR(A2)<=7);C2*$H$4;C2*$H$3)).
    Last edited by jimcot; 05-22-2012 at 09:58 AM.
    Reply With QuoteRegister To Reply
  9. 05-22-2012, 10:18 AM #9
    Creature
    Creature is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate electric costs

    I'm trying to understand your formula. In the weekday function your setting up that sunday=1 en saterday=7 and checking that each cell. Here you are trying to find the weekend days and multiply it with the night price. Okey understand that. Next, Hour = 0 or 7 ? Shouldn't that be 23 and 7?, multiply with night price, else (last semicolon) multiply dayprice. Am I understanding it correctly?

    Sorry to bother, but I tried your formula but I'm still getting an error(see my previous post), could you please upload the example with your modifications?

    Thanks for helping all, I really appreciate it
    Reply With QuoteRegister To Reply
  10. 05-22-2012, 11:08 AM #10
    jimcot
    jimcot is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Calculate electric costs

    In the first part we check whether it's weekend and if it is we use the night price, in the second part we allready know that it's not weekend so we are watching for the time if the time is between 00 (midnight) and 07 (next morning we use the night price else the day price.

    Please see the example (perhaps you will have to change the ; to , for the function to work)

    Sample.xls
    Reply With QuoteRegister To Reply
  11. 06-01-2012, 09:57 AM #11
    Creature
    Creature is offline
    Registered User
    Join Date
    05-22-2012
    Location
    Earth
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Calculate electric costs

    Thanks a lot JiMcOt, works like a charm
    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