Solving for w using Goal Seek???

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240
hHere are the knowns:

w * .15 = x
w * .85 = y
y * .15 = z

(x/12)+y+z = value in cell a1 (this is a given, not a calculation)

so with a1 as the known value is there anyway for me to determine the values for x, y and z?

Thanks!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
If in x/12+y+z=A1 you replace x, y, and z with the w parameter, you will get a formula like c*w=A1.

This gives w = A1/c. And, from there find x, y, and z using the 3 formulas you already have.

No need for Excel. Paper and pencil will be enough. But, one can also use Excel. {grin}
 

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240
Thank you! Can you show me how this would work assuming a1 = 12,000. (Sorry, it's been a while since I've had to do hardcore algebra)

Thanks again!
 

West Man

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
I hope I'm not attempting someone's homework, especially if I get it wrong, but here goes.

a1= (x/12)+y+z so
a1= (x/12)+w
a1= (.15w/12)+w
12*a1=.15w+12w
12*a1=12.15w


w=(12*a1)/12.15
x=.15w
y=1-x
z=.15y
 

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240

ADVERTISEMENT

Not at all...I am a 31yr old Financial Analyst trying to derive the categories (x,y,z) from a single revenue #.

Thanks again!!!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
Yes, unfortunately, you did get it wrong :(

z is in terms of y and not w.

z = 0.15 y
y = 0.85 w

So, z = 0.15*0.85*w

The rest of the well-illustrated algebra should be modified to include the above correction.

I hope I'm not attempting someone's homework, especially if I get it wrong, but here goes.

a1= (x/12)+y+z so
a1= (x/12)+w
a1= (.15w/12)+w
12*a1=.15w+12w
12*a1=12.15w


w=(12*a1)/12.15
x=.15w
y=1-x
z=.15y
 

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240

ADVERTISEMENT

Thanks guys! So using the following worked well using pencil & paper:

x = .15w/12
y = .85w
z = .85w * .15

a1 = (.15w/12) + .85w + (.85w * .15w)

So - using pencil & paper, I can solve this now BUT I want to solve it in excel where A1 is the value in cell A1 and I want x to be returned in A2, y in A3 and z in A4.
 

alyssa75

Board Regular
Joined
May 14, 2007
Messages
240
I've seen goal seek used for this sort of thing but it's just not making sense to me..I can't make it work :(

So first - can someone help me rewrite the above in terms of w (so w= blah blah instead of a1=) and then how can I use goal seek to solve for w.

Thanks much!
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
There's no need for goal seek. It's useful only if one cannot work out a unique solution using algebra.

In your case, you can rewrite the last equation as
w*(0.15/12+0.85+0.85*0.15) = A1
or
w=A1/(0.15/12+0.85+0.85*0.15)

So, to get w in A2, enter =A1/(0.15/12+0.85+0.85*0.15)

To get x in A3 enter =A2*0.15/12

To get y in A4 and z in A5 enter the appropriate formulas keeping in mind that the value of w is in A2.

Thanks guys! So using the following worked well using pencil & paper:

x = .15w/12
y = .85w
z = .85w * .15

a1 = (.15w/12) + .85w + (.85w * .15w)

So - using pencil & paper, I can solve this now BUT I want to solve it in excel where A1 is the value in cell A1 and I want x to be returned in A2, y in A3 and z in A4.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,634
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top