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

1. ## IF Nested Loop for tarrif bill (2003, Xp)

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?
with kind regards

2. ## 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. ## 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. ## 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. ## 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.