Is this Tricky calcualtion Possible?

[rubyh]

Hey guys,
I havent been here in a while thanks to all the previous help over the past. but heres a stumper.

Im trying to come up with a formula to mark up cell A1 enough so that C1 would equal to 10% of B1. No matter what cost price is in B1.

Book1
	A	B	C	D	E	F	G
1	Price	Cost	PR	Fee	Rate	misc 1	MISC 2
2	$112.31	97.5	$8.75	$1.12	$3.65	0.25	1
3	$32.59	28.3	$1.65	$0.33	$1.06	0.25	1
Sheet1

 

[jblackburn]

Try to use the solver. I have not used the solver much but I do know that you set any cell to always = "x" in your case "X" would need to be set at 10% of A1.

 

[rubyh]

cant do that in C2 its already minusing values from other cells how am i to do that?

 

[Oaktree]

Start with 0.1B = A - B - 0.01A - 0.0325A - 0.25 - 1

...add some algebra here...

end with A = 1.148825B + 1.305483

 

[rubyh]

Start with 0.1B = A - B - 0.01A - 0.0325A - 0.25 - 1

...add some algebra here...

end with A = 1.148825B + 1.305483

HUH???? im even more confused now

 

[Oaktree]

C2 =SUM(A2-E2-F2-G2-B2-D2)

D2 = 0.01*A2
E2 = 0.0325*A2
F2 = 0.25
G2 = 1

So, substituting, you get:

C2 = A2 - 0.0325*A2-0.25-1-B2-0.01*A2

You want C2 = 0.1*B2

So, (rearranged) :

0.1B = A - B - 0.01A - 0.0325A - 0.25 - 1

Using some algebra to solve for A, your formula in A2 = 1.148825B2 + 1.305483 per my last post

 

[rubyh]

Can you please explain how to do this? where do i plug these formulas in?

 

[rubyh]

ok ill try that out thanks

 

[Oaktree]

Just replace A2's formula with = 1.148825*B2 + 1.305483. Then, when you adjust B2 to whatever value you'd like, A2 (and subsequently C2) will adjust accordingly.

edit: added *

 

[rubyh]

This is what i have but everytime i enter that formula in A2 i get a formula error not being correct.

Book2
	A	B	C	D
1	Price	Cost	PR	Fee
2	=1.148825B2+1.305483	50	#VALUE!
Sheet1