Power
Billing Solution in Microsoft Excel
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.
How do I view and edit Microsoft Excel files on an iPad?
Inventory Soluton
How to rename and color worksheet tabs
Copy Worksheet Quickly
Criteria Range
Animations in Microsoft Excel
Create two charts at once
How to avoid errors while working in Excel
How to benefit from Microsoft Excel Templates
How to use Microsoft Excel 2007 with Word 2007 - create mail merge labels
Landscape Oriented Worksheet Template
How to-make a worksheet fit a printed page
How to make your charts more impressive
Perpetual Calendar from 1900 to 9999
How to create your own custom add-in
How to clear conditional formattng in Excel worksheets
How to speed up data entry of decimal numbers
How to change the color of the gridlines of an Excel worksheet or hide them
How to use Autosum in Multiple Worksheet Cells Quickly
How to have your free personal assistant in Mcrosoft Excel who reads out the data to you
How to use the status bar in Microsoft Excel to do a quick data analysis
How to calculate equal monthly payments or instalments using a mathematical equation
Data Forms to Enter and Edit Data
View 2 worksheets in same workbook side by side
Accessing a specific worksheet in large workbooks with multiple worksheets
Referencing a cell in another worksheet
How to perform a what-if analysis using a scrollbar form control
An interesting payroll solution
Future Value Solution in Microsoft Excel
Another Interesting Conditional Formatting Solution
DSUM and Array Formulas for addition solutions
IF Function Question
How to use command buttons on a splash screen in MS-Excel
Copy Paste Data from one Excel Worksheet to Another
How to separate comma separated values (csv) in a worksheet cell in Microsoft Excel into rows or columns
Another Interesting Solver solution in Microsoft Excel
Find duplicates in two different Excel worksheets using a macro
How to count data based on multiple criteria - countifs function
How to convert 6/5/2011 into Sunday, 6/5/2011
User Form to perform calculations
An interesting solution using conditional formatting in Microsoft Excel 2007
Show/Hide Controls on a User-Form using a Checkbox
Populate Listbox and Textboxes with Excel data using VBA and Vlookup
Protecting Specific Worksheets in an Excel Workbook using VBA
How to calculate the difference in hours between two date-time values
How do I match data from 2 worksheets and highlight the differences using MS-Excel VBA?
How to focus on a specific control (TextBox1) on a user-form in MS-Excel
Interesting sumproduct solution
COMPARE DATA LISTS USING VLOOKUP AND ISNA FUNCTIONS
VLOOKUP SOLUTION USING LIST IN MS EXCEL
How to generate random numbers in MS Excel
Lottery game in Microsoft Excel
Nested If Function to Determine Project Complexity