Sum of intervals

Asked

Viewed 125 times

4

I needed a formula to calculate values with intervals.

I mean, I have several grades:

Between 0 and 5 = EUR 0,58;

Between 6 and 10 = 0,64;

Between 11 and 15 = €1,05;

Over 15 = 1,46€.

If the number is 7 for example, it has to be calculated as follows:

(5*0,58)+(2*0,64)

It’s by ranks. They can help?

excel

  • I’m not the best Excel expert, but I believe that using maximum and minimum can reach the desired levels. Type, min($A1, 5) * 0.58 + max(min($A1-5, 10-5), 0) * 0.64 (being $A1 a cell with the argument)

  • I believe you can make a giant formula with several SES() or use a Solver with an objective function of the type: FO= x_0 * 0,58 + x_1 * 0,64 + x_2 * 1,05 + x_3 * 1,46 if the values are increasing or decreasing as in the example... and the restrictions of x with the desired range.

  • I’ll set a better example, see if you can help me. Luis - 8 m3 Pedro - 4 m3 Andre - 15 m3 The price of water is calculated using these steps: Between 0 and 5 m3 = 0,58€ Between 6 and 10 m3 = 0,64€ Between 11 and 15 m3 = 1,05€ So I want to know how much each of them will pay for water consumption according to the echelons. And the calculation is done as follows: Luis consumed 8 m3; (50,58) + (30,64) = 4,82€ Pedro 4 m3; 40,58= 2,32 €; Andre 15 m3; (50,58) + (50,64) + (51,05) = 11,35 €. .

  • I understood your problem, I only gave hints as to the possible ways to solve them.

  • 1

    You have to review those intervals there, if it is 5.5 the value drops in limbo. It would probably be: [0, 5[, [5, 10[, [10, 15[... Or [0, 6[, 6, 11[, [11, 16[.

3 answers

1

If the problem has values increasing the way it is proposed (0.58; 0.64; 1;05; 1.46), an optimization function to obtain the minimum solves...

Excel Solver

To perform these tasks without using the VBA or a gigantic formula, the Solver needs to be used and a simple logic is used.

Enable Solver in Excel

The version used is Excel 2010.

  1. Enter the Office or File button.
  2. Click on Options.
  3. Click on Add-Ins.
  4. Install the Solver Add-in and click Ok.

Enable the Add-in

  1. Enable the Developer Mode
  2. On the tab Developer, click on Add-ins
  3. A window will open and the Solver Add-in must be marked.

Solver Add-in

Solver

With the following data in the spreadsheet:

|   | A  |  B   |   C    |      D       |             E              |         F         |
|---|----|------|--------|--------------|----------------------------|-------------------|
| 1 |  0 | 0,58 | =A2-A1 |              | =SOMARPRODUTO(D1:D4;B1:B4) | =VALOR_QUANTIDADE |
| 2 |  5 | 0,64 | =A3-A2 |              |                            |                   |
| 3 | 10 | 1,05 | =A4-A3 |              |                            |                   |
| 4 | 15 | 1,46 |        |              |                            |                   |
| 5 |    |      |        | =SOMA(D1:D4) |                            |                   |

The following objective function and restrictions are added:

![FO

int

y

restricao

somatorio

Solver is added to the cell E1 with the following parameters:

Solver

Upshot

For 4 m³:

4m3

For 8 m³:

8m3

For 15 m³:

15m3

Extra:

To change the optimization function, other forms of objective function or more constraints can be added. If you want to delve into the subject look for Linear Programming, Simplex Method or Solver. Or in English Linear Programming (LP), Simplex or Linear Optimization. A good tool for Excel optimization problems is the Solver Studio.

0

You can use the following table:

inserir a descrição da imagem aqui

Formulas:

C1:=ARREDMULTB.MAT(E1;5)/5

D1:=E1-(C1*5)

E2:=SE(C1=0;D1*B1;SE(C1=1;(B1*5)+(B2*D1);SE(C1=2;(B1*5)+(B2*5)+(B3*D1);SE(C1=3;(B1*5)+(B2*5)+(B3*5)+(B4*D1)))))

Simply vc enter the consumption value in cell E1

The only problem is that the charging interval you determined is fixed.

0

Dude, I don’t know if it will totally help you, but for ranges (ranges) it’s good to make a table with these ranges for example: a1 cell = 0; B1 = 0.58 , a2 cell = 5 ; B2 = 0.58 , A3 cell = 6 ; B3 = 0.64 , A4 = 10 ; B4 = 0.64 and so on. By making the auxiliary table, you can make a procv "TRUE" instead of false, because with the real vc you can pull the intervals of the table instead of exact values. This can help you in your rational calculations in "echelons" (range classes)

  • 2

    Hello @Diego. Try to structure your answer better, you are a little confused.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.