feature for Excel

**Martindelica** · 02-04-2007, 07:58 PM

Hi there, thanks for viewing. I am in need of some help and I am not sure if Excel can do what I need it to do.
If I have a column of numbers, can excel search the numbers and identify i there is a sequence between them? Eg
40.9
43.1
51.3
52.5
51.5
58.1
59.9
64.7
68.3
86.3
81.6
the underlined numbers would light up because, if I ran a MOD, we would see that they are linked by approx. 5.
36+5=41
41+5=46
46+5=51
51+5=56
56+5=61
61+5=66
66+5=71
71+5=76
76+5=81
81+5=86
So I would need a formula that would scan the column and highlight multiple cells that were on the same "axis". Is this possible?

Kindest Regards
Martin.

**Martindelica** · 02-05-2007, 05:26 AM

Hi there, thanks for viewing. I am in need of some help and I am not sure if Excel can do what I need it to do.
If I have a column of numbers, can excel search the numbers and possibly identify a sequence between the numbers? Eg
40.9
43.1
51.3
52.5
51.5
58.1
59.9
64.7
68.3
86.3
81.6
The underlined numbers would light up because, if I ran a MOD function, we would see that they are linked by approx. 5.
36+5=41
41+5=46
46+5=51
51+5=56
56+5=61
61+5=66
66+5=71
71+5=76
76+5=81
81+5=86
So I would need a formula that would scan the column and highlight multiple cells that were on the same "5 axis". Is this possible?

Kindest Regards
Martin.

**Carim** · 02-05-2007, 05:34 AM

Hi,

Are you after any kind of possible correlation ...?

**Loz** · 02-05-2007, 06:32 AM

I don't think Excel has a problem with searching a column of numbers. Tricky bit is to define exactly what the code would be looking for. This will require someone to provide an algorithm for the search. So, something to be worked out on paper initially - then it could be coded up.

If you are only interested in looking for linear relationships, you could investigate the statistical functions within Excel. For example, taking your column of numbers as the 'Y' values of a linear equation (Y=aX+b), you could establish the 'X' values as a simple sequence of 1,2,3,... etc against the column of provided values, then employ say the SLOPE function to obtain the best estimate for the 'a' value and the INTERCEPT function to get the 'b' constant.

The column of numbers provided in your post (once sorted numerically) produced a linear equation of Y=4X+34 (with rounded results) and graphing the straight line against the provided numbers shows a close fit.

It would be possible to test the closeness of fit by comparing the original value against its predicted value and then using the Excel conditional formatting featue to highlight when the two are sufficiently close (e.g. within 1% or 2% or whatever, or indeed if they are an exact match).

There are many other statistical functions, but these may require research to understand first unless Stats. is your thing.

**Martindelica** · 02-05-2007, 08:50 PM

Thanks for responding.
the numbers in the column need to remain intack as they are asociateted to a date. I like the idea of the formula mentioned above. The way I currently do it is, I take my column of numbers and go through different MOD's. Like I would MOD 5 the whole column and then look for cells that have the same or nearly the same result. This would tell me they are on the same axis.
I was hoping there would be something Excel could do to simplify the process, because my way requires me the go through alot of MOD's looking for matches.
HLH.
Kindest Regards,
Martin.

**Loz** · 02-07-2007, 06:14 AM

Certainly using the SLOPE function should give you an indication of the likely linear relationship in the column of numbers.

From this you could then focus your MOD evaluations to, say, either side of the SLOPE value. This would cut down on pure guess work. From your example figures the SLOPE function produces a value of 4.182, suggesting you could check for MOD 3, MOD 4, or MOD 5 as likely candidates for a good fit.

How do you determine which MOD evaluation produces the closest fit to the data? Do you for example calculate the % error between the remainder from the MOD function, and the actual value? Or, something else?

How much data is there to check? Will it ever only be a dozen items, as in your post? If so, then copying and pasting Worksjeet formulae will probably be best, otherwise code may the answer if there's a lot of data.

I would also question the fundamental use of the MOD function for this testing, as using lower-value divisors will surely always produce the closest fit? For example, MOD 2 will always produce matches closer to the actual data values because the remainders will on average be less that the average of the remainders from a MOD 3 or MOD 4. Just a thought.

**Martindelica** · 02-07-2007, 08:40 AM

There can be several hundred cells in a column which is why the method I am doing is very time consuming.
I am not quite that good with excel and have no experience with the SLOPE function, but it does sound promising.
Not quite sure why, with different MOD's I get different results. I hope you could expain the SLOPE formula for me to test. I really appreciate the input.
If I understand this proper, the SLOPE feature would highlight possible matches, then using the MOD on either side would narrow down my parameters.
Brilliant, what formula would I have to use for the SLOPE.

Kindest Regards,
Martin.

**Loz** · 02-08-2007, 06:31 AM

The Excel help provides a description of the SLOPE function as follows...

SLOPE

Returns the slope of the linear regression line through data points in known_y's and known_x's. The slope is the vertical distance divided by the horizontal distance between any two points on the line, which is the rate of change along the regression line.

Syntax

SLOPE(known_y's,known_x's)

Known_y's is an array or cell range of numeric dependent data points.

Known_x's is the set of independent data points.

Remarks

The arguments must be either numbers or names, arrays, or references that contain numbers.

If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.

If known_y's and known_x's are empty or have a different number of data points, SLOPE returns the #N/A error value.
The equation for the slope of the regression line is:

b = ∑(x-x)(y-y)
__________

∑ (x-x)²

where x and y are the sample means AVERAGE(known_x’s) and AVERAGE(known_y’s).

To use the function, put 'x' values of 1,2,3,4.... down the column against your data. Then somewhere on the Worksheet, enter the formula =SLOPE(RngY,RngX) - whatever the ranges are. This will then give the linear slope value.

The attachment examples how it can be used.

Overall, I think your main challenge is to understand the range of statisticical functions that could help you find relationships or patterns in the data, rather than capabilities of Excel.