//
// Function: MULTIPLE.OPERATIONS
//
Value func_multiple_operations(valVector args, ValueCalc *, FuncExtra *e)
{
if (args.count() != 3 && args.count() != 5)
return Value::errorVALUE(); // invalid number of parameters
for (int i = 0; i < args.count(); i++) {
if (e->ranges[i].col1 == -1 || e->ranges[i].row1 == -1)
return Value::errorVALUE();
}
CellStorage *s = e->sheet->cellStorage();
// get formula to evaluate
int formulaCol = e->ranges[0].col1;
int formulaRow = e->ranges[0].row1;
Formula formula = s->formula(formulaCol, formulaRow);
if (!formula.isValid())
return Value::errorVALUE();
CellIndirection cellIndirections;
cellIndirections.insert(Cell(e->sheet, e->ranges[1].col1, e->ranges[1].row1), Cell(e->sheet, e->ranges[2].col1, e->ranges[2].row1));
if (args.count() > 3) {
cellIndirections.insert(Cell(e->sheet, e->ranges[3].col1, e->ranges[3].row1), Cell(e->sheet, e->ranges[4].col1, e->ranges[4].row1));
}
return formula.eval(cellIndirections);
}
Value TestTextFunctions::evaluate(const QString& formula, Value& ex)
{
Formula f;
QString expr = formula;
if (expr[0] != '=')
expr.prepend('=');
f.setExpression(expr);
Value result = f.eval();
if (result.isFloat() && ex.isInteger())
ex = Value(ex.asFloat());
if (result.isInteger() && ex.isFloat())
result = Value(result.asFloat());
return result;
}
void GoalSeekDialog::startCalc(double _start, double _goal)
{
d->widget.label4->setText(i18n("Starting..."));
d->widget.label5->setText(i18n("Iteration:"));
// lets be optimistic
bool ok = true;
// TODO: make this configurable
double eps = 0.0000001;
double startA = 0.0, startB;
double resultA, resultB;
// save old value
if (d->firstRun) {
d->firstRun = false;
d->oldSource = numToDouble(d->sourceCell.value().asFloat());
}
resultA = numToDouble(d->targetCell.value().asFloat()) - _goal;
// initialize start value
startB = _start;
double x = startB + 0.5;
int iterations = d->maxIter;
const Formula formula = d->targetCell.formula();
// while the result is not close enough to zero
// or while the max number of iterations is not reached...
while (fabs(resultA) > eps && (iterations >= 0)) {
startA = startB;
startB = x;
d->sourceCell.setValue(Value(startA));
const double targetValueA = numToDouble(formula.eval().asFloat());
resultA = targetValueA - _goal;
// debugSheets << "Target A:" << targetValueA << "," << d->targetCell.userInput() << "Calc:" << resultA;
d->sourceCell.setValue(Value(startB));
const double targetValueB = numToDouble(formula.eval().asFloat());
resultB = targetValueB - _goal;
// debugSheets << "Target B:" << targetValueB << "," << d->targetCell.userInput() << "Calc:" << resultB;
// debugSheets <<"Iteration:" << iterations <<", StartA:" << startA
// << ", ResultA: " << resultA << " (eps: " << eps << "), StartB: "
// << startB << ", ResultB: " << resultB << endl;
// find zero with secant method (rough implementation was provided by Franz-Xaver Meier):
// if the function returns the same for two different
// values we have something like a horizontal line
// => can't get zero.
if (resultB == resultA) {
// debugSheets <<" resultA == resultB";
if (fabs(resultA) < eps) {
ok = true;
break;
}
ok = false;
break;
}
// Point of intersection of secant with x-axis
x = (startA * resultB - startB * resultA) / (resultB - resultA);
if (fabs(x) > 100000000) {
// debugSheets <<"fabs(x) > 100000000:" << x;
ok = false;
break;
}
// debugSheets <<"X:" << x <<", fabs (resultA):" << fabs(resultA) <<", Real start:" << startA <<", Real result:" << resultA <<", Iteration:" << iterations;
--iterations;
if (iterations % 20 == 0)
d->widget.newValue->setText(QString::number(iterations));
}
d->widget.label5->setText(i18n("New value:"));
if (ok) {
d->sourceCell.setValue(Value(startA));
d->widget.label4->setText(i18n("Goal seeking with cell %1 found a solution:",
d->widget.selector3->textEdit()->toPlainText()));
d->widget.newValue->setText(d->selection->activeSheet()->map()->calculationSettings()->locale()->formatNumber(startA));
d->widget.currentValue->setText(d->selection->activeSheet()->map()->calculationSettings()->locale()->formatNumber(d->oldSource));
} else {
// restore the old value
d->sourceCell.setValue(Value(d->oldSource));
d->widget.label4->setText(i18n("Goal seeking with cell %1 has found NO solution.",
d->widget.selector3->textEdit()->toPlainText()));
d->widget.newValue->setText("");
d->widget.currentValue->setText(d->selection->activeSheet()->map()->calculationSettings()->locale()->formatNumber(d->oldSource));
}
enableButtonOk(ok);
enableButtonCancel(true);
}
