Featured Post

Complete Parts and Function of Computer Keyboard

BELOW ARE THE MOST PARTS AND FUNCTIONS OF THE COMPUTER KEYBOARD THAT COMMONLY USED. A computer keyboard is used to enter commands of...

How to Calculate Electrical Bills Using Excel 2007

How To Calculate Electrical Bills Using Excel 2007

Using your computer you can calculate your electrical bills using the power of Microsoft Office Excel 2007. Installed in your computer system unit(CPU). In this application to calculate these electrical bills, you need first to create a worksheet’s table then input your worksheet data to be calculated.


This application of the Microsoft excel 2007, this is better if in your area, you are using solar panels that you officially own or you’re the property owner. And there are plenty of your customers connected to your electricity using the solar system as your source of electrical power. You can calculate it's of power consumption and how much they will pay every month.

To begin with, this application, open up your Microsoft Excel 2007 or higher versions and follow the instruction below to create this electrical bills:



CREATE YOUR WORKSHEETS TABLE

Create your worksheets table with worksheet’s title above and input this following worksheet’s label: House No., House Owner, Previous Unit, Current Units, Unit Consumes, Electrical Charges, Surcharge, Bill Payable, Status.


CALCULATE THE FOLLOWING DATA
  • Unit consumes- it is the current units minus the previous unit.
use this formula: =D5-C5
  • It is calculated based on the unit consumes equals the electrical charges.
LECTRICAL CHARGES:

Unit consume is less than or equal to 100 rates per unit is equal to 11
unit consume between 101 to 200, the rate per unit is equal to 13
unit consume above 200, the rate per unit is equal to 17


=ROUNDUP(IF(E5>200,E5*17,IF(E5>100,1000+(E5-100)*13,E5*11)),4)
  • SURCHARGE CALCULATION-it is the electrical charges multiply to the percentage of the electrical additional charges.
=ROUNDUP(IF(F5>5000,F5*0.15,IF(F5>1000,F6*0.07,0)),1)

HOW TO APPLY COLOR CODE AS STATUS
you need to create status like what I use in my worksheets
Status Percentile Using Conditional Format

green- haft pay
50%
red- not pay
10%
blue- paid
100%


Follow this instruction using the conditional format:
  1. Highlight the bill payable
  2. Then click home and then conditional formatting
  3. Then click the new rule under the drop menu list
  4. Under the window select “Format all cells base on their values”
  5. Then under the format style, click drop-bar then select 3 color scale
  6. Then under the minimum click/select percent, then input the percentage given above.
  7. Repeat the procedure 6 for the midpoint and maximum
  8. Then click ok


Review your worksheets if the calculation is all correct and the status of the color is applied to the exact payment if it is paid, haft paid, and fully paid. See the post picture above. If you have any questions regarding this application kindly leave it in the comment bar. See you next post.

No comments:

Post a Comment

Note: We have Zero Tolerance to Spam. Avoid Promotions with 'Links' considered as spam will be deleted immediately upon our review.

MAY YOU LIKE THIS POST!