Calculate or input value

If you want to do this with three cells, exactly as you have described, you need VBA.

To do this with only formulas requires another cell with conditional formatting. Here is the same solution shown twice, putting the values in two different cells. The user puts a value in B1 or C1, then D1 shows the calculated value.

Values as displayed

	A	B	C	D
1	Some Value	Percentage	Value	Value
2	100	10%		10
noshali

Underlying formulas

	A	B	C	D
1				=IF(OR(B2&C2="",AND(B2<>"",C2<>"")),"",IF(B2<>"",C1,B1))
2				=IF(OR(B2&C2="",AND(B2<>"",C2<>"")),"",IF(B2="",C2/A2,B2*A2))

Conditional Formatting Rules

Formula
Applies To	Formula	Format	Stop If True
$D$2	=$C$2<>""	0%	False
noshali

Values as displayed

	A	B	C	D
1	Some Value	Percentage	Value	Percentage
2	100		10	10%
noshali

I can do a VBA solution too if you want to go that direction.

It's better to give a complete explanation of your question up front. My answer addressed exactly what you asked in the first post.

The same solution can be applied to as many rows as you want. If you use a Table, then the formula will automatically be added if you add rows. The only problem with multiple rows is you cannot have the dynamic header because some rows will be value, some will be percentage.

If you still cannot work out how to apply this solution to your file, please attach a sample file. See yellow banner at the top of the page.

You can't do this with a formula. A cell can contain EITHER a formula OR a hard-coded value, but not both. You'd need VBA.

In your file, the relationship is between column D and E, not E and F. Are you sure you want to let the user change the value in column F? If you do, it's a different question than what you asked in your first post.

Values as displayed

	D	E	F
3	Completed	Percentage	Current
4	to Date	Complete	Invoice
5		(D/B)	(D-C)
6
7	$ 3,000.00	15%	$ 1,000.00
D17 (2)

Underlying formulas

	D	E	F
3
4
5
6
7	=B7*E7		=D7-C7