Speed Distance Time formula

**neomagik** · 01-11-2011, 07:52 AM

Hi,

I've wondered this for a while.

Say cell A1 is Speed, B1 is Distance, C1 is Time.

I want to write a formula in each cell that is dependant on the value of the other two but I don't want the value I enter to overwrite the formula.

For eg. I want to calculate time so I enter values in the distance and speed cells. I then want to calculate speed so I delete what I previously entered in the speed cell and overwrite the answer it just gave me for time with my new value.

Is there a simple inbuilt function to do this?

Thanks

Rob

**Palmetto** · 01-11-2011, 08:04 AM

Input cells and formula cells must be independent. If you have a formula cell and then enter a value into the same cell, the formula is over-written.

I suggest you set up the input cells for each variable and include an additional cell for a single letter entry of T, D, or S as a flag for which calculation to perform. Then in the formula cell test the value of the flag and perform the calculation:

If A1 was the "flag" cell, we might use:

=IF(A1="T",time formula,IF(A1="D",distance formula,speed formula))

S formula becomes the default calculation in the above formula.

**martindwilson** · 01-11-2011, 08:11 AM

nope youll have to repeat , values in any 2 of three cells and then 3 different calculations in other cells
like this

**jwright650** · 01-11-2011, 08:16 AM

Just a note...keep your units of measure apples to apples so the result will make sense.

**neomagik** · 01-11-2011, 08:16 AM

Thanks guys, I know you can do it this way but just thought there may be something simpler.

**martindwilson** · 01-11-2011, 08:19 AM

how simple do you want it!that is probably as simple as excel formulas get,now someone could probably write code to do it but would that be simpler?

**neomagik** · 01-11-2011, 10:00 AM

Originally Posted by martindwilson

how simple do you want it!that is probably as simple as excel formulas get,now someone could probably write code to do it but would that be simpler?

Thanks Martin but the format of my spreadsheet does not lend itself to your way of doing it.

I only used the speed distance time as an example as this is a very simple formula.

I actually need it for a much more complex formula I just wanted to know if I there was a built-in excel function that allows a group of cells to be linked by a common formula of which each cell is assigned a specific part of the formula so that changing any one value will change the rest in that group but as it turns out, there isn't.

Of course writing code would be harder and I don't expect anyone to do that!!

**arthurbr** · 01-11-2011, 11:10 AM

I actually need it for a much more complex formula I just wanted to know if I there was a built-in excel function that allows a group of cells to be linked by a common formula of which each cell is assigned a specific part of the formula so that changing any one value will change the rest in that group but as it turns out, there isn't.

If you could provide a small example sheet with what you want to do ,we could probably find a solution to your problem