Excel step electricity price calculation method (example detailed explanation)

This article brings you relevant knowledge about excel, which mainly introduces the calculation method of tiered electricity prices. Laddered electricity prices refer to setting a household's electricity consumption into several steps. , segmented or graded pricing calculation costs, let’s take a look at it together, I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

Ladder electricity price refers to setting a household’s electricity consumption into several steps. Segmented or tiered pricing calculates fees.

The specific calculation method is:

The first step is the basic electricity, and the electricity price in this step is the lowest;

The second step is for the electricity consumption higher than the basic electricity. For one part, the electricity price executed will be higher than the basic electricity price;

The third tier is for the remaining electricity consumption that is higher than the second tier, and the electricity price executed for this part will be higher.

Let’s use an example to show you how to perform such calculations.

As shown in the figure below, they are the grading standards and corresponding electricity prices for each level:

As shown in the figure below, it should be based on the usage of column B. Electricity bill is calculated based on the amount of electricity used.

Enter the following formula in cell C2 and copy it down:

=SUM(TEXT(B2-{0,260,600},”0;!0″)*{0.68,0.05, 0.25})

Let’s briefly talk about the meaning of this formula.

B2 in the formula is the electricity consumption this month.

The 260 and 600 in {0,260,600} are the power consumption limits for the first and second gears respectively.

The 0.68 in {0.68,0.05,0.25} is the electricity price of the first grade;

0.05 is the price difference between the second grade and the first grade (0.73-0.68=0.05);

0.25 is the price difference between the third level and the second level (0.98-0.73=0.25).

You can understand the calculation method of the formula in this way:

1. Among all the electricity consumption, the first price is 601, that is A2-0kWh.

2. The price difference between the second level and the first level is calculated by subtracting the total power consumption from the first level, which is A2-260. The result is 341.

3. The price difference between the third level and the second level is calculated by subtracting the total power consumption from the second level, which is A2-600, and the result is 1.

Finally, multiply the electricity consumption of different levels by the corresponding price difference, that is:

0.25*1, 0.05*341, 0.68*601

Use the SUM function to sum up, and you can get the electricity bill.

What does the TEXT part of the formula mean?

TEXT(B2-{0,260,600},”0;!0″)

Because when the total electricity consumption is at the lowest level, then subtract it from the total electricity consumption The stop code of each gear will have a negative number. For example, if the total power consumption is 200, use 200-{0,260,600} to get such a memory array:

{200,-60,-400}

That is to say, execute the first The electricity price for the first gear is 200, while the second and third gears become negative.

Write the second parameter of the TEXT function as "0;!0". The purpose is to force the negative number to 0, that is, to change the memory array {200,-60,-400} into {200,0,0 }.

After processing in this way, the electricity consumption for the first level of electricity price is 200, and the electricity consumption for the second and third levels is 0.

In actual application, you can modify the constant parameters {0,260,600} in the formula and the difference between each gear {0.68,0.05,0.25} according to the data situation.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel step electricity price calculation method (example detailed explanation). For more information, please follow other related articles on the PHP Chinese website!