Forum Home
Press F1
Thread ID: 42135	2004-02-01 23:49:00	XL again	B.M. (505)	Press F1

Post ID	Timestamp	Content	User
212178	2004-02-01 23:49:00	Here’s a little XL problem for all you XL guns. I actually solved it myself once using “Solver” in XL (I think) but have somehow misplaced the spreadsheet and can’t for the life of me think how I did it. I think I can do it with pencil and paper but that is not as much fun now is it? The problem goes like this: I want to sell my house and require a certain amount of money in my hand after the (thieving, did I say that?) Land Agents have taken their commission. So, the Land Agent wants: $400 base fee 4% of the first $250,000 2% of the balance + GST @ 12.5% on the above. Now, let’s say I want $350,000 in my hand, what must the thieves sell the house for, for me to achieve the $350,000 clear? Your time starts “Now” :) The workings please no just the answer :D	B.M. (505)
212179	2004-02-02 00:15:00	B.M. Hmmmmm how about something like the following: Labels: A1='House Sale Price B1='Agent Fixed Fee C1='4% of 1st $250k of House Sale D1='2% Balance of House Sale E1='GST @ 12.5% of total Agents fees F1='My Balance Values: A2 <somevalue> that you will plug into the sheet B2 400 (fixed value) C2 =SUM(250000*0.04) D2 =SUM((A2-250000)*0.02) E2 =SUM((B2+C2+D2)*0.125) F2 =SUM(A2-(B2+C2+D2+E2)) Giving an answer of $364271 for the house sale price? You could do a slightly reverse setup fixing your output value (My balance) to $350k etc... Anyways my way of doing it for what it's worth. Cheers, Babe.	Babe Ruth (416)
212180	2004-02-02 00:26:00	$364271 or I guess $364500 would be bottom line. You don't want to know. Added back the fixed fees with gst included (being the $400 base and the 4% on 250,000) then iterated the rest. If $365000 is in C5: =C5+(ROUND(400*1.125,2))+ROUND(250000*0.04*1.125,2 ) Used this calc where C8 is the result of the above and includes my guess as well. =(2%*1.125)*(C8-250000) Couldn't be arsed doing a proper iteration. Bear in mind that the purchaser does not care about what you want to get, but more about what they want to pay. robo.	robo (205)
212181	2004-02-02 00:27:00	Skinned different ways but got the same result. phew. robo.	robo (205)
212182	2004-02-02 00:40:00	Very good guys, Home run for Babe and Robo can go to the top of the class. You may be interested to know that neither method even slightly resembled my lost spreadsheet, so there must be at least a third way of doing it. I’m absolutely sure I used the solver add-in as I can remember having to load it as it doesn’t automatically load with the installation of XL. All good stuff isn’t it. Cheers Bob	B.M. (505)
212183	2004-02-02 01:37:00	Using Babe's formulas you can use the Goal Seek function which is found under tools on the Excel menu. If you then set F2 to be $350,000 and by changing cell to be A2 then it will automatically jump to a solution. Note that Goal Seek is essentially a very basic version of the Solver addin. It doesn't always converge to a solution so when it fails that's when it's time to the more sophisticated solver addin (where you can play around with the algorithms it users etc). You may also want to use the solver function for adding constraints on how the solution converges, find a min/max etc. But I digress... Cheers Dave	odyssey (4613)
212184	2004-02-02 01:46:00	Dahhhhhh, the penny’s just dropped Babe, another senior moment! That method of yours was using solver and I think, as the grey matter slowly flows, about the same as my original method. Got that sorted now I’ll move onto Robo’s and see how stuffed up I can get there. Thanks again Bob	B.M. (505)
212185	2004-02-02 01:47:00	Yea, thanks Dave, It’s not a facility one uses all that often and I’m real rusty, which will be obvious to all by now. Cheers Bob	B.M. (505)
1