Results 1 to 6 of 6

20070717, 23:13 #1
 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 (0100) is $2.65/unit, and the cost for the unit from above hundred to three hundred (101300) 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

20070717, 23:25 #2
 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.

20070717, 23:25 #3
 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+(A1100)*3.64,993+((A1300)*6.15)))
Steve

20070718, 19:13 #4
 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(A3A2,B7C2)
=MAX(0,B7A3)
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.

20070718, 19:40 #5
 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(A3A2,B7C2)
A3A2 is the maximum number of units to be charged at $3.64, and B7C2 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,B7A3)
B7A3 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.

20070718, 20:11 #6
 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.