Dynamic Formula

[gregmpenn]

Is there a way to create something of a dynamic formula in excel that works in dual directions?
For example, down payment amount (in dollars) vs. down payment percentage.
I have a calculator that you can input a percentage and it’ll give you the dollar amount of a purchase price.
Example:
$400,000 purchase x’s .0020 = $80,000.

Cell A: $400,000
Cell B: %
Cell C: (sum/formula cell)

Cell C is essentially dependent on cells A and B.
Is there a way, though, to make cell B dependent on cells A & C at the same time?
So, that if I change the dollar amount in C, it’ll update the percentage in B? AND vice versa?

Right now I can only get the amount based on the percentage, but the percentage cell obviously doesn’t change when I update the amount cell.

 

[Micron]

If I understand the ask I'd say no. First, if you edit C you will wipe out the formula. Second, if you could employ your idea it would create what's known as a circular reference (where a value depends on another value but the other value depends on the first value). The only way I can see being able to come close to what you want would be to use vba, and a userform would make more sense than trying to do it on a sheet.

 

[Excel Novice 1]

Given this is a 3 variable equation, I don't think there is a possible solution if 2 variables are unknown. Excel will need to recognize if the input value is a percentage or a $ amount since there can only be 1 variable to reach a solution (this is a mathematics issue not excel limitation). People with more knowledge can confirm if that's true or not however I'm not aware of a way.

I think you can make it work if you include a field to answer that question. There are 2 input fields: The value and the variable identifier. Based on those, the formulas can recognize how to solve the equation. The formulas in the example below assume the purchase price is always an input. Based on whether or not the input type is a $ or %, the A3 and A4 will know which formula to use to solve for the other variable.

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	Purchase price	400,000					Purchase price	400,000
3	Down payment %	20%		Input type	Down Payment $		Down payment %	20%		Input type	Down Payment %
4	Down payment $	80,000		Input amount	80,000		Down payment $	80,000		Input amount	20.0%
Sheet1

Cell Formulas
Range	Formula
B3,H3	B3	=IF(E3="Down Payment $",B4/B2,IF(E3="Down Payment %",E4))
B4,H4	B4	=IF(E3="Down Payment $",E4,IF(E3="Down Payment %",B2*B3))

 

[Excel Novice 1]

A thought to remove the second input field for the variable type is that the formula in B3 and B4 could be modified so that if the input value in E4 is less than 1 then the formula will treat it as a percentage and if it is greater than 1 then it treated as a $ amount

 

[gregmpenn]

That makes sense, thanks.

 

[Micron]

All that rings true, and as I see it, is a matter of logic. If this, then that. If this but not this, then something else; etc. That's why I see vba as the solution but I have nothing concrete to offer at this point because what has been revealed doesn't lend itself to suggesting anything definitive.