Adding a portion of a formula with an operator from another cell

[IamBatman]

I'm creating a spreadsheet that shows what numbers are if we do an increase. We might do that increase by a specific amount or by a percentage. This means my formula might be 16+.25 or 16*1.01

I would like to be able to have that section of the formula that is doing the increasing (+.25 or *1.01) be able to be input from another cell. Is there a way to do that and include the operator (+ or *)? Or would I need to set up two cells and alter my formula?

For context, this is a simplification. The 16 is will actually be a cell reference, and this formula will be in 30 non-continuous cells.

 

[Bosquedeguate]

Try this

Book5
	A	B	C	D
1	Operand 1	Operator	Operand 2	Result
2	6	+	4	10
3		*		24
4		^		1296
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=EVAL($A$2,B2,$C$2)

You will need to create a VBA function like the following
the values would be in separate cells (see function call above)

Public Function Eval(operand1, operator, operand2) As Variant
  Eval = Evaluate(Join(Array(operand1, operator, operand2), " "))
End Function

Will a function like this do what you need?

 

[Peter_SSs]

Could you use something like this. H2 contains Data validation which has a choice between "Amount" and "Percentage"

23 08 30.xlsm
	A	B	C	D	E	F	G	H	I
1		Original	New		Original	New		Increase Type	Increase
2		16	16.25		18.26	18.51		Amount	0.25
3
4		203	203.25
5					100	100.25
Increase

Cell Formulas
Range	Formula
C2,F5,C4,F2	C2	=(B2+IF($H$2="Amount",$I$2,0))*IF($H$2="Amount",1,$I$2)

Cells with Data Validation
Cell	Allow	Criteria
H2	List	Amount,Percentage

Changing H2 & I2:

23 08 30.xlsm
	A	B	C	D	E	F	G	H	I
1		Original	New		Original	New		Increase Type	Increase
2		16	16.16		18.26	18.4426		Percentage	1.01
3
4		203	205.03
5					100	101
Increase

Cell Formulas
Range	Formula
C2,F5,C4,F2	C2	=(B2+IF($H$2="Amount",$I$2,0))*IF($H$2="Amount",1,$I$2)

Cells with Data Validation
Cell	Allow	Criteria
H2	List	Amount,Percentage

 

[IamBatman]

Thank you both. I'd rather not use VBA if I can help it, so I went with the formula option.

The amount part works, but not the percentage part. Not sure what I've got wrong (cell references updated for my sheet).

=J2+IF($I$1="Amount",$J$1,0)*IF($I$1="Amount",1,$J$1)

I also included a picture of the section in question

 

[IamBatman]

Thank you both. I'd rather not use VBA if I can help it, so I went with the formula option.

The amount part works, but not the percentage part. Not sure what I've got wrong (cell references updated for my sheet).

=J2+IF($I$1="Amount",$J$1,0)*IF($I$1="Amount",1,$J$1)

I also included a picture of the section in question

Well that's silly. I flipped the addition and multiplication around for kicks and giggles, and now both work.

=J2*IF($I$1="Amount",1,$J$1)+IF($I$1="Amount",$J$1,0)

 

[Peter_SSs]

Not sure what I've got wrong

You are missing a set of parentheses
=(J2+IF($I$1="Amount",$J$1,0))*IF($J$1="Amount",1,$J$1)

My original formula was
=(B2+IF($H$2="Amount",$I$2,0))*IF($H$2="Amount",1,$I$2)

 

[Peter_SSs]

I see that you seem to have resolved this. However, your idea of doing the multiplication first is good too.

 

[IamBatman]

You are missing a set of parentheses
=(J2+IF($I$1="Amount",$J$1,0))*IF($J$1="Amount",1,$J$1)

My original formula was
=(B2+IF($H$2="Amount",$I$2,0))*IF($H$2="Amount",1,$I$2)

Those pesky parentheses. Thank you!

 

[Peter_SSs]

Those pesky parentheses. Thank you!

Cheers