Automation trial and improvement formula help

[h4yd3n]

ok guys, needed a little bit of help

what it is, i want to find the best cost to improve the profit and revenue, now i can do this by trial and improvement but im too lazy to do that, like what is excel for, right?

so i wanted a formula which will test which cost will produce the highest profit

example

[TABLE="width: 500"]
[TR]
[TD]100[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]200[/TD]
[TD]700[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]250[/TD]
[TD]750[/TD]
[/TR]
[TR]
[TD]275[/TD]
[TD]780[/TD]
[/TR]
[TR]
[TD]300[/TD]
[TD]400[/TD]
[/TR]
[/TABLE]

thanks
any questions, let me know

 

[Joe4]

Welcome to the Board!

So what exactly do those two columns of numbers represent?
What exactly is your process?
Can you explain to us how you would do it "by trial" so we can understand the logic you are using?

 

[h4yd3n]

Welcome to the Board!

So what exactly do those two columns of numbers represent?
What exactly is your process?
Can you explain to us how you would do it "by trial" so we can understand the logic you are using?

Hi joe,

So what i was the project is, is for property rental, and i have formulae on other worksheets within the excel document and as i increase the cost to rent out the property, the total revenue increases, but once past the perfect price, the revenue will decrease back down until it hits 0. its basically an example of price elasticity. Now rather than me inputting figures in and out and making a guess, i want to find a formula which will go through the process of finding the right cost which will maximise the profit.
Has that made it any clearer?

Thanks

 

[Joe4]

Has that made it any clearer?

Unfortunately not, at least not without actual formulas or data. You haven't told us what those two columns of numbers are in your original post, and what your other Excel formulas are. We need to know that in order to help you with that.

Note you cannot attach files here. But you can post images. There are instructions on how you can post images found in the "Posting Aids" section of this link here: http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html. There is also a "Test Here" forum on this site that you can use to test those tools before using them in your threads.

 

[B___P]

For this kind of simulation analysis I often use one or two input data tables.

Bye

 

[h4yd3n]

For this kind of simulation analysis I often use one or two input data tables.

Bye

how would you do that?

 

[Smitty]

Welcome to the Board!

You might want to look at Solver, which is part of the Analysis Toolpak add-in.

HTH,

 

[B___P]

It looks like excel is going to forget this Data Table, it is hidden in Data Instruments (I translate on the fly..), simulation analysis, data table. The online help is missing.
Data tables for simulation may have one or two inputs.
For One input tables I pick a cell as the input cell (a1) and the one at his right as the output cell (b1). I put into the destination cell a formula with reference to input cell.
Only for example a1=1 b1=a1^3 then I fill cells under a1 with a serie of possible data (may be till a100). Select the range from a1 to b100, then simulation analysis, data table. Excel will prompt for "cell input for row" and "cell input for column". You have to pick the option suitable for your data layout (column in the example and a1 as reference). The selected range will be filled with the results of your formula near the data serie

The example is very poor but if the output cell b1 has a link to a result of a more complex algorythm in other sheet and this algorithm takes the table's input cell as its own input is clear that data table is fantastic for simulations.

For two input tables input data have to be in a row and in a column with the corner cell containing the formula.

This way you have a table with inputs and outputs very usefull combined with conditional formatting and charting.

bye

 

[h4yd3n]

Welcome to the Board!

You might want to look at Solver, which is part of the Analysis Toolpak add-in.

HTH,

Finally got it!!!!

thanks

solver was perfect!!!!!!!!