// // 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); }