-1

I'm creating a LibreOffice calc spreadsheet but I can also use Excel if this will make things easier.

The spreadsheet is based around a scientific formula with 3 variables, let's call them a, b and c. To be honest, there is also a 4th variable, d. However, d is just c in a different representation (like in d=1/c). Sometimes, it's more intuitive to enter c, sometimes d.

OK, I end up with 4 variables. It is necessary for the user to enter 2 of them, the missing 2 can be calculated easily (except if the user enters c and d, this will not work of course).

Let's say column A, B, C and D are for entering the variables a, b, c and d. I want my spreadsheet to calculate the missing two values. That's no problem. Each cell is prefilled with the necessary formula.

However, imagine the user to enter a and b. c and d will now be calculated and displayed. But by entering a and b, the formulas for calculating a and b are overwritten! It's now not possible to clear b and enter c instead. If the user want to enter different variables he has to copy the formulas from "backup cells".

To make it short: I want the cell to contain their formulas even if a value is entered. Is this possible without macros? I can think of a solution using helper cells...

Community
  • 1
  • 1
lugge86
  • 255
  • 3
  • 11
  • 1
    No, either use helper cells where the input and output are in different cells or use vba. – Scott Craner Nov 28 '17 at 17:38
  • 2
    Why tag the question with [vba] if you are specifically wanting a **non**-VBA solution? – YowE3K Nov 28 '17 at 18:57
  • I believe my answer may do what you ask. However, it is somewhat vague because your question did not provide specific details. – Jim K Nov 28 '17 at 22:00

1 Answers1

0

Add a different scenario for each possibility. For example, start with the following formulas.

A      B        C
~~~~~  ~~~~~~~  ~~~~
=B1+1  =A1+1.5  =A1+B1

To begin with, all of these formulas depend on each other, resulting in a circular dependency Err:522.

Now, select all three cells and create a scenario with Tools -> Scenarios. Name the first one "All Formulas" and press OK.

Create three more scenarios, one for each variable, named "Variable A", "Variable B", "Variable C".

Then select the scenario named "Variable A" and enter a value in cell A1. Likewise, use "Variable B" when setting B1 to a value.

The "Variable C" scenario in this example is a little trickier because it depends on both A1 and B1. Thankfully, Calc provides a tool for this as well. Go to Tools -> Solver and set Target cell to C1, By changing cells to either A1 or B1, and then specify the desired resulting number in Value of.

solver

For my example, it may be necessary to use two scenarios for variable C, one in which By changing cells is A1 and another in which it is B1. So the name could be something like "Variable C by changing A".

Jim K
  • 12,824
  • 2
  • 22
  • 51