Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Nested Loop for tarrif bill (2003, Xp)

    Respected Sir/Madam
    I want to know how to make if nested loop. Actually i am working on Tarrif Bill where i failed to make a logical statement.
    My problem is. I am willing to make a tarrif bill. The bill contains electricity charges. suppose if the cost of electricity for first hundred (0-100) is $2.65/unit, and the cost for the unit from above hundred to three hundred (101-300) is $3.64/unit, and above 300 unit it cost $6.15. How i can calculate the amount of bill on excel sheet?
    Anxiously waiting for your kind reply
    with kind regards

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Nested Loop for tarrif bill (2003, Xp)

    Welcome to Woody's Lounge!

    See the attached sample workbook. The usage can be entered in cell B7, and the total tarriff is calculated in cell D7. The cells C24 contain intermediate results: the number of units in each stage, and the tarriff for that stage.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF Nested Loop for tarrif bill (2003, Xp)

    If the value is in A1:

    =IF(A1<=100,A1*2.65,IF(A1<=300,265+(A1-100)*3.64,993+((A1-300)*6.15)))

    Steve

  4. #4
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Nested Loop for tarrif bill (2003, Xp)

    Thankyou Sir (Hans & Steve) to solve my problem. I am really thankful to both of you and this forum as well who provide me opportunity to learn from your experience.
    Actually sir Hans your way is new for me. May i ask what is the meaning of these commands which you mentioned in your attached sheet.
    =MIN(A2,B7)
    =MIN(A3-A2,B7-C2)
    =MAX(0,B7-A3)
    Actually i know it's work excellently for me. but i don't know how to define these terms. Would you please define these terms for me. Please pardon me for my ignorance.
    Thankyou
    With kind regards.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Nested Loop for tarrif bill (2003, Xp)

    The MIN function returns the minimum (lowest value) of its arguments, and MAX returns the maximum (highest value) of its arguments.

    =MIN(A2,B7): A2 contains the boundary 100, and B7 contains the usage. If the usage is less than 100, the usage is returned, and if the usage is 100 or more, 100 is returned. This is the number of units charged at $2.65 per unit.

    =MIN(A3-A2,B7-C2)

    A3-A2 is the maximum number of units to be charged at $3.64, and B7-C2 is the usage over 100 units (0 if the usage is less than 100). The minimum of these is the number of units to be charged at $3.64.

    =MAX(0,B7-A3)

    B7-A3 is the number of units over 300; if B7 is less than A3, this difference is negative, so we take the maximum (highest) of 0 and this difference. This is the number of units to be charged at $6.15.

  6. #6
    New Lounger
    Join Date
    Jul 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Nested Loop for tarrif bill (2003, Xp)

    excellent reply.
    Now my concept is clear
    Thankyou.

Posting Permissions

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