ansaurus

Question

How to determine a formula for execution time given quantitative data, Excel, trendlines, monte carlo simulation

Answer 1

A:

Create a chart in Excel, add a trendline, and select to have the equation displayed on the chart.

Lance Roberts 2009-07-12 10:53:34

I can take a subset of the data and create a chart with trend line for iterations (x) vs time (y) and view the formula (holding obligors constant), I can also do this separately for obligors (x) vs time (y) for a given number iterations (holding this constant). But I have a grid of data with both variables iterations, obligors and time, how to get a formula for T = some function with O (obligors) and I (iterations).Thanks

m3ntat 2009-07-12 11:13:26

Answer 2

+2 A:

I'm not quite sure of the data involved due to the question construction/presentation.

Assuming you're looking for y = f(x). If you load the data into Excel, you can use the methods SLOPE and INTERCEPT on the data ranges to derive an expression of the form

y = mx+c

and thus a linear function.

If you want a quadratic or cubic, you can use LINEST with a column of time data squared/cubed etc. to give you quadratic/cubic parameters, and thus derive an appropriate higher order function.

Brian Agnew 2009-07-12 11:13:03

Thanks Brian, I've used the Slope function and now have two sets of slope data. The slopes for time = as obligors increase for given numbers of iterations, and the slopes for time = as iterations increase for given numbers of obligors.Slopes for increasing obligor runs 200,400,600,800,1000,1200,1400,1600,1800,200020000 0.07526615440000 0.15434589160000 0.2297527180000 0.302824147100000 0.383819553120000 0.449864445

m3ntat 2009-07-12 11:37:58

AndSlopes for increasin iterations for runs 20000,40000,60000,80000,100000,120000200 0.000746733400 0.001486137600 0.002222518800 0.0029704271000 0.0037304391200 0.004464521400 0.0051743911600 0.0060338151800 0.0067109062000 0.007549094Do I need to run through this for intercept as well? Then how to get an overall formula for Time as a function of number of obligors and number of iterations?Thanks

m3ntat 2009-07-12 11:38:55

So firstly get your y=mx+c for both data sets. Now (it's hard to do this without a diagram), you have a linear function for time vs obligors, and time vs iterations. I'm not an expert at this, but imagine those two functions plotted at right angles to each other, sharing the time as a vertical axis. You need to find some function that weights both contributions (such that looking at one only, you don't consider the other). I confess I'm not sure how to do that.

Brian Agnew 2009-07-12 11:47:40

I've posted up the question here http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24563392.html which also allows file attachments, the data I am working with is here:http://filedb.experts-exchange.com/incoming/2009/07_w29/158924/trends.xls not sure if you will be able to see this without an EE account though.

m3ntat 2009-07-12 12:29:52

linking to experts-exchange.com when it is subscription only is spam surely.

polyglot 2009-07-12 13:56:41

Well there is no way to attach a file here or format a table of data, and google docs is blocked at my work. To spam is to send something unsolicited, usually of a commercial nature with some alterior motive, non of which applies in this case. Polyglot, if there is a policy surrounding this, please point me to the FAQ or thread discussing this. Thanks

m3ntat 2009-07-12 14:41:03

I've just formatted it as code, which makes it look a little better

Brian Agnew 2009-07-12 14:50:08

Great thanks Brian, much better will use the code option next time.

m3ntat 2009-07-12 14:52:12

Answer 3

A:

To clarify: You have tabular data below which you want to fit to some funtcion f(O,I)=t?

        200          400         600         800         1000        1200        1400        1600        1800        2000
20000   15.627681   30.0968663 44.7592684 60.9037558 75.8267358 90.3718977 105.8749983 121.0030672 135.9191249 150.3331682
40000   31.7202111  62.3603882 97.2085204 128.8111731 156.2443206 186.6374271 218.324317 249.2699288 279.6008184 310.9970803
60000   47.0708635  92.4599437 138.874287 186.0576007 231.2181381 280.541207 322.9836878 371.3076757 413.4058622 459.6208335
80000   60.7346238  120.3216303 180.471169 241.668982 300.4283548 376.9639188 417.5231669 482.6288981 554.9740194 598.0394434
100000  76.7535915  150.7479245 227.5125656 304.3908046 382.5900043 451.6034296 526.0730786 609.0358776 679.0268121 779.6887277
120000  90.4174626  179.5511355 269.4099593 360.2934453 448.4387573 537.1406039 626.7325734 727.6132992 807.4767327 898.307638

A rough guess looks like both O & I are linear. So f is in the form t = aO + bI + c. Plug in a few (O,I,t) and see what a,b,c should be.

Ray 2009-07-12 13:12:19

Great so how do I go about calculating a,b and c?

m3ntat 2009-07-12 13:23:26

Answer 4

+1 A:

Spoke to one of the quants here the function is of the from T = KNO, where T is time, K some constant, N iterations, O obligors.

Rearrange for K = T/(NO), plug this into my sample data, take the average of all sample points, use the Std dev for the error

I did this for my data and get:

T = 3.81524E-06 * N * O (with 1.9% error), this is a pretty good approximation.

m3ntat 2009-07-16 09:41:16

ansaurus

tags:

views:

answers:

How to determine a formula for execution time given quantitative data, Excel, trendlines, monte carlo simulation

related questions