Estimate values

**m_k** · 12-19-2012, 11:57 AM

I have two columns

Rank - This is a traffic rank with less the rank better the traffic is..
Visitors - Number of visitors, more the number of visitors more the traffic

i have given set of values for 44 entities. now for 45th if i have one of the two above then can we predict based on the value we have. For example if i have the rank for 45th item then can i predict visitors for the same from the values already present (44 items)?

Rank Visitors
A1 142,870 17,397
A2 638,762 7,956
A3 281,693 2,136
A4 82,292 526
A5 43,547 125,771
A6 37,515 240,977
A7 866,378 3,812
A8 9,624 150,196
A9 3,196,466 1,162
A10 492,230 4,003
A11 6,926 890,473
A12 15,125 32,219
A13 8,244 360,577
A14 678,823 1,224
A15 105,530 69,869
A16 28,311 172,740
A17 49,813 175,432
A18 268,966 7,840
A19 262,854 50,005
A20 1,476 3,151,844
A21 106,575 73,830
A22 35,922 912
A23 80,405 115,006
A24 174 19,811,301
A25 89,109 17,352
A26 34 2,909
A27 267,028 1,878
A28 860,590 5,145
A29 283,336 25,081
A30 325,354 9,806
A31 30,484 116,778
A32 215,303 24,533
A33 136,494 34,538
A34 37,098 12,437
A35 28,928 408,084
A36 53,785 22,653
A37 531,639 700

A38 1,456,301 3,142
A39 26,150 1,338
A40 1,742,423 1,582
A41 64,695 ??
A42 10,501 ??
A43 2,286,615 ??
A44 31,466 ??

I used forecast/trend function but it didn't produce right result..

Thanks in advance
-m

**Ace_XL** · 12-19-2012, 04:30 PM

Try

=TREND($C$2:$C$41,$B$2:$B$41,B42)

what are your expected results?

**Miraun** · 12-19-2012, 04:39 PM

Well... quickly viewing over the data points provided... I'm seeing very loose, if any, correlation within the data, unless there are a vast quantity of outliers.

On one hand, A26 has a rank of 34, with 2,909 visitors, but then rank A24 has a rank of 174 with 19,881,301 visitors. Then we jump to A9's Rank 3,196,466... but it has 1,162 visitors.

I'm really not seeing the correlation. If you go onto the 2007's ribbon, go to Formulas, More Functions, Statistics, within those list of equations, there are trending analysis for various types of regressions, including logrimatic, linear, exponential, and variety of things I've never even heard of. Your best bet is to apply one of those types of regression models to the data for your results... ... ... as long as you can justify that type of model.