Power Billing Solution Using the If Function in Microsoft Excel

A question from one of our website visitors:

Hello Sir/Madam,

This is Rajkamal. Am in the beginning stage or we can say as fresher also. Sir I want to use the cell values (Which use to be enter in that particular cells) in “for and If conditions” in VBA programming.

Sir Actually I have Contract Demand, Billing Demand & Maximum Demand. So my payment as to do according to the billing demand but in case my maximum demand is higher than billing demand then I have to pay the maximum demand charges. In that case if the maximum demand exceeds the contract demand then I have to pay penalty for that. Here I can explain by giving the sample values.

Case1:

Contract Demand = 100 kW

Billing Demand = 75 kW

Maximum Demand = 110 kW

In this case I have to pay for that 110 kW charges, up to 100kW no penalty charges. Rest of 10 kW I have to pay penalty.

Case 2:

Contract Demand = 100 kW

Billing Demand = 75 kW

Maximum Demand = 90 kW

In this case I have to pay for that 90 kW charges, no penalty charges.

Case 3:

Contract Demand = 100 kW

Billing Demand = 75 kW

Maximum Demand = 50 kW

In this case I have to pay for that 75 kW charges, no penalty charges.

In this second problem is, in EB they have the classifications like,

First 30kW charges Rs.80

Then next 20 kW charges Rs.100

Then next 15 kW Charges Rs. 120

Then rest of kW charges Rs.150

Penalty Charges Rs.180/kW

In this the split up some time it will change. Like sometimes it may only two split ups

First 50kW charges Rs.90

Rest of kW charges Rs.130

Penalty Charges Rs.180/kW

So I want whenever we are giving split up according that, it has to calculate.

With this mail I have attached the excel sheet in which I tried little bit but I couldn’t able to complete please help me to resolve this one. Thanks in advance. Anymore clarifications please ask me.

Solution:

First break the problem into small segments

Next we calculate the power consumed in cell D2==IF(AND(C2>B2,C2>A2),C2,IF(AND(C2>B2,C2<A2),C2,IF(AND(C2<B2,C2<A2),B2,” “))) Does the consumer have to pay penalty? Cell E2==IF(D2>100,D2-100,0)

Calculate the penalty amount: Cell F2==IF(D2>100,(D2-100)*180,0)

Now calculate the total payable amount: Cell G2==IF(D2>=65,(D2-E2-65)*K2+15*J2+20*I2+30*H2+F2,IF(D2>=50,(D2-50)*120+20*100+30*80,IF(D2>=30,(D2-30)*100+30*80,IF(D2>=0,D2*80))))

In cell M2==IF(AND(A2>=100,C2>B2,C2>A2),(C2-A2)*L2+(100-65)*K2+J2*15+I2*20+H2*30,IF(AND(A2>=50,C2>B2,C2>A2),(A2-50)*J2+I2*20+H2*30,IF(AND(A2>=30,C2>B2,C2>A2),(A2-30)*I2+H2*30,IF(AND(A2>=0,C2>B2,C2>A2),H2*C2,IF(AND(A2>=100,C2>B2,C2<A2),(C2-65)*K2+J2*15+I2*20+H2*30,IF(AND(A2>=50,C2>B2,C2<A2),(A2-50)*J2+I2*20+H2*30,IF(AND(A2>=30,C2>B2,C2<A2),(A2-30)*I2+H2*30,IF(AND(A2>=0,C2>B2,C2<A2),H2*C2,IF(AND(A2>=100,C2<B2,C2<A2),(B2-65)*K2+J2*15+I2*20+H2*30,IF(AND(A2>=50,C2<B2,C2<A2),(B2-50)*J2+I2*20+H2*30,IF(AND(A2>=30,C2<B2,C2<A2),(B2-30)*I2+H2*30,IF(AND(A2>=0,C2<B2,C2<A2),H2*B2,” “)))))))))))), we have calculated the payable amount in one shot

The last function in cell M2 can be used to create an user-form or a command button to automate the whole calculations.