I needed a formula to calculate values with intervals.
That is, I have several levels:
Between 0 and 5 = 0.58 €;
Between 6 and 10 = 0.64;
Between 11 and 15 = 1.05 €;
Above 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 steps. Can you help?
Dude, I do not know if it will help you fully, but for ranges it is good to make a table with these ranges, for example: cell a1 = 0; b1 = 0.58, cell a2 = 5; b2 = 0.58, cell a3 = 6; b3 = 0.64, a4 = 10; b4 = 0.64 and so on. By doing the auxiliary table, you can do a procv "TRUE" instead of false, because with the true you can pull the ranges of the table, instead of exact values. This can help you in your rationale of "step" calculations (interval classes)
To accomplish these tasks without using VBA or a gigantic formula, the solver needs to be used and simple logic is used.
The version used is Excel 2010.
Withthefollowingdataintheplan:
||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)|||Thefollowingtargetfunctionandconstraintsareadded:
ThesolverisaddedincellE1withthefollowingparameters:
For4m³:
For8m³:
For15m³:
Tochangetheoptimizationfunction,otherformsofobjectivefunctionormoreconstraintscanbeadded.Ifyouwanttodelveintothesubject,lookforLinearProgramming,SimplexMethodorSolver.OrinEnglishLinearProgramming(LP),SimplexorLinearOptimization.AgoodtoolforExceloptimizationproblemsisthe Solver Studio .