Here’s a conundrum for you (or me actually, but any pointers would help).

I’m measuring the tension in a wire versus the movement of something on the end of that wire – the something is normally supported and the idea is to measure how much pull it takes to get it moving. In theory you’d see a small linear movement due to elastic stretch in the wire as the tension increases (measuring is done on the wire, not the weight), then a different linear movement as the tension overcomes the weight and the whole thing starts moving. Unfortunately noise, recording accuracy and general real life complications mean the two linear bits are a bit fuzzy and the transition between the two theoretically linear sections is a bit indeterminate.

So I need to fit a line to the first (elastic) part of the movement, a line to the second part of the movement and then the intersection between those two is a pretty good indication of the number I’m looking for. Trouble is it’s hard to spot the difference between the two sections of data to do the line fitting – I’m thinking along the lines of finding 2 lines (4 unknowns) and a transition point (1 unknown) that minimises the mean squared error of the data from the lines, but not sure that gives me enough to solve the problem.

I’ve got a degree in hard sums so can deal with mathsy stuff, can do all the Excel hacking but just wondered if anyone had any cunning pointers to likely looking algorithms/methods.

doesn’t really sound like a job for excel.

matlab?
scipy?

jam bo – agreed, but unfortunately Excel is what it needs to be done in (don’t ask!).

I need to saw a block of wood in half, and I have to use this spongecake.

If someone’s mandating tools for a job, get them to do it, no?

Some kind of cluster analysis to sort out the data groups (maybe k-means clustering), then draw the lines based on the clusters?

No idea how you’d do it in Excel, I’d be inclined to download R-Studio and use that. And it’s free. And generates pretty pictures if that’s what you need.

Edit: seems you can do it Excel, a quick google has turned up a number of ways.

Cheers mogrim – I’ve found a paper on another method that looks promising too. Cougar – it’s not quite that simple – it’s an alternative to a current solution that could be rolled out to many sites, but isn’t likely to be accepted if it needs extra bells and whistles to achieve it.

EDIT – added link

Cheers mogrim – I’ve found a paper on another method that looks promising too.

You’re welcome. I’d look into machine learning stuff, the Wiki page is a good start – for example http://en.wikipedia.org/wiki/Cluster_analysis

What are you actually trying to measure calculate “the number I’m trying to calculate”

Yes the wire will stretch a bit. The force required to get the object moving will probably be greater than to keep it moving, so once the object moves if you keep pulling with the same force the object will accelerate and the wire will spring back some amount too. Are you, measuring force, movement, speed ? If you keep repeating the experiment the stretch in the wire will change (especially if it’s new)and will the resistence between the object and the surface.

FYI I have used cluster analysis quite a bit, its very useful for helping to think about relationships between different variables and/or functions of them

isn’t likely to be accepted if it needs extra bells and whistles to achieve it.

They can do without, then. Honestly, there’s no helping some people.

(-:

Have you tried using a longer bit of wire? Bit simplistic I know!

Definitely more suited to R Studio or Minitab.Failing that you can get several add-ins for Excel.Wrong tool for the job so you probably won’t get any really useful advice imho